upobDaPlaya
asked on
Set a worksheet when looping thru worksheets
I think I am still struggling with SET and explicit references. I have the below lines and I always gate a value for A3, but never for A4. It says A4 is null even though there is a value in A4.
If I explicitly set a reference to "A4" then I have no issue and I get a value for A4. However, I will never know my worksheet name so I can not explicitly set the worksheet name..How do I ensure I get a value for A4.
Dim ws1 as worksheet
Dim strValue
For Each ws1 in ThisWorkbook.Worksheets
If ws1.Range ("a3"). value Like "Orange" Then
strValue = ws1.Range ("a3"). value
Else
End if
If ws1.Range ("a4"). value Like "Volunteer Center A" Then
strValue1 = ws1.Range ("a4"). value
Else
End if
.........
.........
.........
Next ws1
If I explicitly set a reference to "A4" then I have no issue and I get a value for A4. However, I will never know my worksheet name so I can not explicitly set the worksheet name..How do I ensure I get a value for A4.
Dim ws1 as worksheet
Dim strValue
For Each ws1 in ThisWorkbook.Worksheets
If ws1.Range ("a3"). value Like "Orange" Then
strValue = ws1.Range ("a3"). value
Else
End if
If ws1.Range ("a4"). value Like "Volunteer Center A" Then
strValue1 = ws1.Range ("a4"). value
Else
End if
.........
.........
.........
Next ws1
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
If you wanted the value from the current sheet you would just use ActiveSheet, there would be no need to loop.
By the way, using Like without any wildcards is basically the same as using =.
If ActiveSheet.Range ("a3").Value Like "Orange" Then
strValue = ActiveSheet.Range ("a3"). Value
End If
If ActiveSheet.Range ("a4").Value Like "Volunteer Center A" Then
strValue1 = ActiveSheet.Range ("a4").Value
End If
By the way, using Like without any wildcards is basically the same as using =.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Imnorie..i need to go to each sheet. If a4 has a value i do a bunch of calculations in the current sheet and then go on to the next sheet..
Then there's nothing wrong with the code you've posted, apart, perhaps, the issue of an undeclared variable raised by Saqib.
If there is a problem in the code perhaps it's with the code you've not posted?
If there is a problem in the code perhaps it's with the code you've not posted?
ASKER
Essentially i am trying to find out if i need to loop thru each worksheet and grab the a4 value when the worksheet is unknown then what is the proper structure. .sorry for the confusion..note a4 will always have a value in each sheet
Like I said there is nothing wrong with the code you posted.
It will loop through each worksheet and grab the values in A3 and A4, if they equal 'Orange' and 'Volunteer Center A' respectively.
It will loop through each worksheet and grab the values in A3 and A4, if they equal 'Orange' and 'Volunteer Center A' respectively.
ASKER
its bizzarre..If i go to the immediate window and do ? ws1.Range("A1") it gives me a value from another worksheet ! If i do ? ws1.Range("A3") it correctly gives me the value of the worksheet that has focus in the loop.
This is for a volunteer project which has donor information on it which is why I am not sending the spreadsheet. Any more ideas...If not I will remove all the donor info nad attach..
This is for a volunteer project which has donor information on it which is why I am not sending the spreadsheet. Any more ideas...If not I will remove all the donor info nad attach..
ASKER
Ok...I hunted around the web a little and did the below and it worked...Just in case someone else has the same issue..Nice thing about the below is I know my worksheets that I need to review will be starting at 12 so I am not wasting resources checking 1 to 11
Sill would be curious on why I was having the issue though...
For h = 12 to ActiveWorkbook.Sheets.Coun t
Set ws1 = ThisWorkbook.Worksheets(h)
...............
.......................... ...
Sill would be curious on why I was having the issue though...
For h = 12 to ActiveWorkbook.Sheets.Coun
Set ws1 = ThisWorkbook.Worksheets(h)
...............
..........................
Glad that worked but I really don't see any problem with the original code you posted.
Can you post the rest of the code, and if possible attach a cleaned up version of the workbook?
Can you post the rest of the code, and if possible attach a cleaned up version of the workbook?
ASKER
Thanks...
ASKER