Workbook Open VBA to Loop through Rows...Struggling with the progression

[jaredmccullough]

I am currently modifying some code to loop through each row and perform a sequence that has a value in column A. This will be my first time trying a loop progression and I am trying to understand where my issue might lie as I keep getting an error on the if statement which makes me think that I am not appropriately naming or wording something correctly. I have tried calling out the cell value, I have tried using the range and I guess as not doing this before I am lost at how you would define the object (i.e. the cell value of the row that is selected in column A is not empty). Sorry very new to all of this so this may be a really dumb question.

Please Login or Register  to view this content.

[xladept]

Maybe:

Please Login or Register  to view this content.

[jaredmccullough]

Maybe:

Please Login or Register  to view this content.

It seemed to get me to the next step....if I might ask what is the X+3 doing? My goal was to select the row, perform the code in the row, then move to the next row. Now I am getting an error on the next line of code which was similar format of (Range("S").Value) but my guess is my idea of using Range then column in the code is not working is it because I do not have range defined?

[xladept]

Well your range starts in the fourth row, so A1:A3 aren't data. How about posting your whole code?

[jaredmccullough]

Well your range starts in the fourth row, so A1:A3 aren't data. How about posting your whole code?

Makes sense now I was under the impression that by setting N and selecting the range it would start at row 4 automatically I must have interpretted that wrong.

Please Login or Register  to view this content.

[jaredmccullough]

Bump for any help....

[thatandyward]

It's also possible to just start the loop at 4, would have the same effect but would make the code a little easier to read.

I think your other issues come from using .Select and then using lines like Range("S").Value, which is not a valid Range object.

Much better to explicitly address the cells you need to manipulate/get data from. You should amend your code so it uses the Cells(row,column) notation, this allows you to substitute x (current loop number) into the row position and then indicate the respective column as necessary.

You can wrap your code in a With statement for the Worksheet your data is on then just use .Cells(x,19).Value to refer to column S in the current loop iteration row. note the preceding . as that references the Worksheet from the With statement.

I did adjust your code to reflect these changes but the site is not letting me post it - some kind of firewall block comes up saying there is SQL injection in my post! not sure what that is about?

[thatandyward]

copied the code to a text file here

FYI it might be easier to move this code to a module whilst you're testing/debugging. that would allow you to run the code and step through line-by-line as necessary, rather than relying on the Workbook Open event to trigger it.

[xladept]

Can't test without a sample but try it this way:

Please Login or Register  to view this content.

[jaredmccullough]

Can't test without a sample but try it this way:

[/CODE]

I no longer received an error but the code seemed to do nothing which infers that there must be some sort of mis arrangment of sequence within. By theory when I open the workbook all those without values should have went to Error.

[jaredmccullough]

copied the code to a text file here

FYI it might be easier to move this code to a module whilst you're testing/debugging. that would allow you to run the code and step through line-by-line as necessary, rather than relying on the Workbook Open event to trigger it.

Thatandyward,

I attempted to adjust via the provided code...the context sequence you provided makes sense to me but after attempting it I got an error on this line I attempted to make a few changes no one which worked.:

Please Login or Register  to view this content.

[xladept]

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

[jolivanes]

If you have empty cells/rows, the following does not work. It goes to the first empty cell/row.

Please Login or Register  to view this content.

If you use part of your code and insert a MsgBox in there you'll see why.
Try this (part of your code Post #1) and see if the answer you get is what you think it should give you.

Please Login or Register  to view this content.

End Sub

Have not checked all your code but you probably want something like

Please Login or Register  to view this content.

[thatandyward]

I did a find and replace on the "Row" to change it to "Rw" for my variable names and it erroneously also changed the .Row on the end of that line to .Rw.

Apologies, that line should be LastRw = Range("A4"_.End(xlDown).Row

[jolivanes]

Deleted message.
thatandyward has not properly read my post but he/she is not the thread starter anyway.

[bakerman2]

I'm probably missing something but by changing this code runs till the end.

Please Login or Register  to view this content.

[thatandyward]

I was responding to a specific query jared had about the code I wrote for him, I thought it would be useful to explain the error I made as it might be helpful with the helping-people-learn-VBA function of this forum.

I guess I should have included a quote to the relevant section of the thread but I was just replying quickly.

[jaredmccullough]

If you have empty cells/rows, the following does not work. It goes to the first empty cell/row.

Please Login or Register  to view this content.

If you use part of your code and insert a MsgBox in there you'll see why.
Try this (part of your code Post #1) and see if the answer you get is what you think it should give you.

Please Login or Register  to view this content.

End Sub

Have not checked all your code but you probably want something like

Please Login or Register  to view this content.

I ran the suggested code and came up with the accurate count. As background every row until the last row populated will have data in column A but I understand what your suggesting and think that either format would work in my situation.

[jaredmccullough]

I'm probably missing something but by changing this code runs till the end.

Please Login or Register  to view this content.

Bakerman,

You are correct that if I modify the original code with the suggested corrections it will operate to completion. My problem at the moment is that after the numbers change for the day I have no way currently (not sure if anyone has suggestions) of how to trial this code in that it is essentially adjusting the numbers based on the Date the workbook was opened.

[jaredmccullough]

I was responding to a specific query jared had about the code I wrote for him, I thought it would be useful to explain the error I made as it might be helpful with the helping-people-learn-VBA function of this forum.

I guess I should have included a quote to the relevant section of the thread but I was just replying quickly.

Andy,

Thanks for the reply and I greatly appreciate the explanation as it better helps me understand the details to what we are talkinga bout. Unfortuantely I fixed this issue but received an error on the next dataline on the "Rw" as well

[jaredmccullough]

For continuity the suggestion in Post #16 added to the original code accomplished the intended results based on current trials. I will mark as solved and appreciate everyones help.