Macro Looping Inexperience :-(

Hi AlphaFrog!

Many many thanks for your reply - I will be putting it into use today!

Would you be willing to add commentary to explain each line of code not explained?

There are some terms I am not used to using / understand what it does. If you could explain? I will research today but thought it would be easier to understand linked to this example:

- i As Long

- .Range

- End(xlUp)

- UBound(PAYNUM, 1)

- IsError(Application.Match(PAYNUM(i, 1), .Columns(2), 0))


As you might have guessed from my previous examples I am only really used to looping through cells using 'For i = 1 to 1000 ... Next i' so the way this has been done is quite foreign to me


Again many thanks for your help - greatly appreciated!


Coeus

Hi again,

I have researched a bit but any input youd be willing to input would be great still!

I have expanded on the original macro to run through 12 worksheets to match / input the PAYNUM from.

I get a 'Type Mismatch' error though when trying to get it to loop?

Could someone have a look and advise - it would be greatly appreciated!


Sub REFRESH_DATA()


'Defines PAYNUM as the variable for payroll numbers.
Dim PAYNUM As Variant, i As Long

'Turns off screen updating.
Application.ScreenUpdating = False


For j = 1 To 12
'****************************** STORE VARIABLES ******************************

'Sources sheet to store variables from.
With Sheets("Data - Month " & j)
'Stores each value in column A as a variable.
PAYNUM = .Range("A1", .Range("A" & Rows.Count).End(xlUp)).Value

End With


'****************************** COMPARE VARIABLES *****************************


'Sources sheet to compare to.
With Sheets("Summary")

'Loop through each PAYNUM variable.
For i = 1 To UBound(PAYNUM, 1)

'Tests if the PAYNUM variable matches any value in column B.
If IsError(Application.Match(PAYNUM(i, 1), .Columns(2), 0)) Then

'If no match to PAYNUM variable is found adds value to the first empty cell in column B.
.Range("B" & Rows.Count).End(xlUp).Offset(1).Value = PAYNUM(i, 1)

End If

Next i

End With

Next j

'****************************** CLOSE *****************************


'Turns on screen updating.
Application.ScreenUpdating = True


End Sub

I don't think there is anything wrong with the code so I think it could be an issue with your data. You might need to add some error handling. For example, UBound(PAYNUM,1) will return a type mismatch error when PAYNUM is not an array, so if column A has only one cell wth data, or has no data at all this will return the error you describe. Try this:

Please Login or Register  to view this content.

Hi again all!

Thanks for the help and feedback to date really appreciate it! Learning so much!

I am a bit stuck with a slight modification to the macro code - basically instead of pasting a single cell as PAYNUM I would like it to paste a range of cells whilst still comparing the first cell?

Can someone highlight what I'm doing wrong?



Sub REFRESH_DATA()


'Defines PAYNUM as the variable for payroll numbers.
Dim PAYNUM As Variant, i As Long

'Turns off screen updating.
Application.ScreenUpdating = False


For j = 1 To 12
'****************************** STORE VARIABLES ******************************

'Sources sheet to store variables from.
With Sheets("Data - Month " & j)
'Stores each value in column A as a variable.
PAYNUM = .Range("A1", .Range("A:D" & Rows.Count).End(xlUp)).Value

End With


'****************************** COMPARE VARIABLES *****************************


'Sources sheet to compare to.
With Sheets("Summary")
If IsArray(PAUNUM) = True Then
'Loop through each PAYNUM variable.
For i = 1 To UBound(PAYNUM)

'Tests if the PAYNUM variable matches any value in column B.
If IsError(Application.Match(PAYNUM(i, 1), .Columns(2), 0)) Then

'If no match to PAYNUM variable is found adds value to the first empty cell in column B.
.Range("B" & Rows.Count).End(xlUp).Offset(1).Value = PAYNUM(i, 1)

End If

Next i
End If
End With

Next j

'****************************** CLOSE *****************************


'Turns on screen updating.
Application.ScreenUpdating = True


End Sub

Hi all!

Can anyone help me on the above - been fiddling and researching cannot get the thing to work! :-(


Coeus.

Thanks AlphaFrog!


I will try it out today and let you know how I get on.

Then I need to start pouring over it and understand the code!


Coeus.

Hi AlphaFrog!


I've just tried that code and no data is now pulling in compared to my original :-(


Is it because of the values it is trying to match the loop on is now a range?


Would you mind taking another look?


Regards,

Coeus.

AlphaFrog has a keen eye.

Many many thanks for your help with this project - it has made a task very efficient. Promise you I am going to learn every line of this code and understand it completely!

My very last request. I tried reversing the 'For J = 1 to 12' to 'For J = 12 to 1'. It didn't work and the 12th sheet will contain the most recent data for a code, if not the 11th, if not the 10th etc. Is there a way to set this?

I think it may be to do with the 'With Sheets("Data - Month " & j)' line?


Hope you've had a good day!


Coeus.