Solved

Set a worksheet when looping thru worksheets

Posted on 2014-03-16
12
206 Views
Last Modified: 2014-03-16
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
0
Comment
Question by:upobDaPlaya
  • 6
  • 5
12 Comments
 
LVL 33

Accepted Solution

by:
Norie earned 450 total points
ID: 39932769
The code is looping through every worksheet in the workbook and looking at A4 on each of those worksheets.

Are you sure A4 on every worksheet has a value?

Are you sure you want to loop through every sheet?

If you do then strValue and strValue1 will always take their values from A3 and A4 of the last sheet.
0
 

Author Comment

by:upobDaPlaya
ID: 39932801
Yes i want to loop thru each sheet..how do i get the value of the current sheet
0
 
LVL 33

Expert Comment

by:Norie
ID: 39932809
If you wanted the value from the current sheet you would just use ActiveSheet, there would be no need to loop.
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

Open in new window


By the way, using Like without any wildcards is basically the same as using =.
0
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 
LVL 43

Assisted Solution

by:Saqib Husain, Syed
Saqib Husain, Syed earned 50 total points
ID: 39932813
You have a dim statement only for strvalue but not for strvalue1. Are you sure that there are two distinct variables?
0
 

Author Comment

by:upobDaPlaya
ID: 39932831
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..
0
 
LVL 33

Expert Comment

by:Norie
ID: 39932833
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?
0
 

Author Comment

by:upobDaPlaya
ID: 39932834
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
0
 
LVL 33

Expert Comment

by:Norie
ID: 39932835
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.
0
 

Author Comment

by:upobDaPlaya
ID: 39932853
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..
0
 

Author Comment

by:upobDaPlaya
ID: 39932904
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.Count
Set ws1 = ThisWorkbook.Worksheets(h)
...............
.............................
0
 
LVL 33

Expert Comment

by:Norie
ID: 39933046
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?
0
 

Author Closing Comment

by:upobDaPlaya
ID: 39933410
Thanks...
0

Featured Post

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Drop Down List with Unique/Distinct Values (Part II - ComboBox or ListBox and Data Validation List Bonus!) David Miller (dlmille) Intro This article focuses on delivering unique, sorted lists to list objects (e.g., ComboBox, ListBox) and Dat…
Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …

832 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question