PDA

View Full Version : Do While question



andytpl
09-04-2007, 06:56 PM
I have worksheet that summarises the outstanding actions from a series of worksheet with name as R1, R2 R3...
I have a macro that pull in the specific data from these worksheets. At the present moment the worksheet name, the data from Cell B8 and the data starting from Row55 Col B onwards are displayed. There is a condition set that if column 14 is empty data from Row55 Col B will be displayed.
What I would like to do now is the same condition but also apply to worksheet name and data from cell B8. That is to say if the condition is met display the data if not blank.
As this is beyond me I hope to get some help to solve this from this forum. A sample file is attached I hope will give more clarity to what I am requesting
Thanks

Oorang
09-26-2007, 07:19 AM
Hmm I think your description of problem needs a little clarification (simplification), but if you just need to check values on multiple sheets you can do something like this:
Option Explicit

Sub ExampleCheck()
Const lngNoValue_c As Long = 0
Dim ws As Excel.Worksheet
Dim blnValueFound As Boolean
For Each ws In ThisWorkbook.Worksheets
If VBA.LenB(ws.Range("B1").Value) <> lngNoValue_c Then
blnValueFound = True
Exit For
End If
Next
If blnValueFound Then
Sheet1.Range("A1").Value = "Foo"
End If
End Sub

andytpl
09-26-2007, 05:33 PM
I agree, my description is not clear and I will try again to see if I can make it clearer.

Worksheet named Summary pulls in data from a series of worksheet with name such as R1, R2 R3 and so on. There are 2 conditions to be met before the data are pull into Summary sheet.

If Cell K3:N3 (merged cell) is empty pull in the data from R1, R2, R3 ... and display the name of sheet into column A and from B8:N8 (merged cells) to column B starting from row 10. If not, nothing is display

Second condition, if column 14 starting from row 55 is empty corresponding data from Row55 Col B will be displayed. If not, nothing is display.