Skipping a line in Excel Macro if it fails to meet a condition

[krhirish0581]

Hi All!

First time posting to the board. I've looked all over to find an answer to this, but nothing seems to fit my needs. To set up the problem, I have two worksheets (same workbook). One sheet has the raw data, and the second sheet has the processed data. The data that is placed in the raw data sheet comes from a text file, and I cannot change the format. For each person, there are several rows in the raw data, which I process with the macro to place the data I need into different columns in the processed data sheet (one row per person). It cycles through every line per person in the raw data, pastes the data into the processed data sheet, then moves on to the next person once the ID Number doesn't match anymore. All this stuff works fine.

What I need is to be able to update the data in the processed data sheet for those that are still in the organization while still maintaining the rows for the people who have left the organization (thus, no more raw data for that person). The best way I can think to do this would be to skip the row in the processed data sheet and go to the next person, but I can't figure out how to do it. Here's the code:

' Define ID for both raw and processed FITREP Data
idraw= Sheets("Raw").Cells(currentRow, idColumn)
idprocessed= Sheets("Processed").Cells(processedRow, idColumn)

' Will process until ID in Raw sheet is blank
Do While idraw <> ""

'   Matches raw data with processed data, continues until all entries for one person are complete
    Do While idraw = idprocessed
    
'       Copies Trait Average from current Raw Row from Raw Sheet and pastes in Processed sheet
        Application.ScreenUpdating = False
        Sheets("Raw").Select
        Sheets("Raw").Cells(currentRow, traitavgColumn).Select
        Application.CutCopyMode = False
        Selection.Copy
        Sheets("Processed").Select
        Sheets("Processed").Cells(processedRow, processedtraitavgColumn).Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        
'       Copies RSCA from current Raw Row from Raw Sheet and pastes in Processed sheet
        Sheets("Raw").Select
        Sheets("Raw").Cells(currentRow, rscaColumn).Select
        Application.CutCopyMode = False
        Selection.Copy
        Sheets("Processed").Select
        Sheets("Processed").Cells(processedRow, processedrscaColumn).Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        
' Go to the next Row
        
    currentRow = currentRow + 1
    
' Go to the next set of TA and RSCA columns on the Processed Sheet

    processedtraitavgColumn = processedtraitavgColumn + 2
    processedrscaColumn = processedrscaColumn + 2
    
    idraw = Sheets("Raw").Cells(currentRow, idColumn)
    idprocessed = Sheets("Processed").Cells(processedRow, idColumn)
    
    Loop

' Go to the next person in the Processed Sheet
processedRow = processedRow + 1

' Resets the TA and RSCA columns for the new person
processedtraitavgColumn = 25
processedrscaColumn = 26

' Resets idraw and idprocessed for the next Loop
idraw = Sheets("Raw").Cells(currentRow, idColumn)
idprocessed = Sheets("Processed").Cells(processedRow, idColumn)

Loop

Application.ScreenUpdating = True

 

[Puertorekinsam]

How do you know if the person is no longer in the organization? Is there a notification

You should be able to process this with a simple:

"IF flag = indicator THEN"
right after the Do While I Draw

and "END IF"
Before ProcessedRow= ProcessedRow+1

 

[krhirish0581]

There is no notification. Basically, if they are no longer in the organization, then there will be no data in the "Raw" Sheet for them. I have other subs that import the data and ensure both the processed and raw sheets are in alphabetical order by name. So say I have 4 names:
Anderson
Baker
Jones
Smith

Jones is no longer in the organization, but I have to retain his data in the processed sheet. The raw data will only have rows for Anderson, Baker, and Smith. I want to macro to get down to Jones, see there is no data for him in the Raw Sheet, skip his name in the processed sheet, and move on to Smith (I will also want to highlight Jones' row in an interior color)

 

[Puertorekinsam]

Okay... It would help if we had the whole code, but I think this should work. (I had to make some guesses on the variables you used... so please save your work before trying..

' Define ID for both raw and processed FITREP Data
idraw = Sheets("Raw").Cells(Currentrow, idColumn)
idprocessed = Sheets("Processed").Cells(ProcessedRow, idColumn)

' Will process until ID in Raw sheet is blank
Do While idraw <> ""
    

   If idraw <> idprocessed Then
    Sheets("Processed").Rows(ProcessedRow).Interior.Color = RGB(102, 255, 255)
   Else

'   Matches raw data with processed data, continues until all entries for one person are complete
    Do While idraw = idprocessed
    
'       Copies Trait Average from current Raw Row from Raw Sheet and pastes in Processed sheet
        Application.ScreenUpdating = False
        Sheets("Raw").Select
        Sheets("Raw").Cells(Currentrow, traitavgColumn).Select
        Application.CutCopyMode = False
        Selection.Copy
        Sheets("Processed").Select
        Sheets("Processed").Cells(ProcessedRow, processedtraitavgColumn).Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        
'       Copies RSCA from current Raw Row from Raw Sheet and pastes in Processed sheet
        Sheets("Raw").Select
        Sheets("Raw").Cells(Currentrow, rscaColumn).Select
        Application.CutCopyMode = False
        Selection.Copy
        Sheets("Processed").Select
        Sheets("Processed").Cells(ProcessedRow, processedrscaColumn).Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        
' Go to the next Row
        
    Currentrow = Currentrow + 1
    
' Go to the next set of TA and RSCA columns on the Processed Sheet

    processedtraitavgColumn = processedtraitavgColumn + 2
    processedrscaColumn = processedrscaColumn + 2
        idraw = Sheets("Raw").Cells(Currentrow, idColumn)

    idprocessed = Sheets("Processed").Cells(ProcessedRow, idColumn)


    
    Loop
    End If
' Go to the next person in the Processed Sheet
ProcessedRow = ProcessedRow + 1

' Resets the TA and RSCA columns for the new person
processedtraitavgColumn = 25
processedrscaColumn = 26

' Resets idraw and idprocessed for the next Loop
idraw = Sheets("Raw").Cells(Currentrow, idColumn)
idprocessed = Sheets("Processed").Cells(ProcessedRow, idColumn)

Loop

Application.ScreenUpdating = True

 

[krhirish0581]

This worked great thanks!!! I think I was previously just putting the conditional statement in the wrong place. I make sure to put the definitions in the code for my next post.

 

[Puertorekinsam]

Glad it worked.