PDA

View Full Version : Solved: Help with For....Next, and some useful code for everyone



intel122
04-05-2008, 10:21 AM
Hi guys. First time poster, long time lurker. I thought it's time I started contributing to this forum.

I am having problems with a For.....Next loop (I think that is what I need to use).

I have a series of worksheets (how many, varies, between a 'start' and 'end' sheet) that I wish to collect a cell value from, and put the names of those sheets, along with the value of the cell (or better, the formula =sheet!cellx) into one worksheet, like a consolidation.

I have attached the workbook which makes more clear what I want to achieve.

So to show I am not completely selfish, here is the code for creating an index sheet which hyperlinks to all the other sheets within the workbook automatically. I noticed on some other forums that a lot of people were having trouble trying to get it to work on Excel 97, because this was written for Excel 2000 onwards. The original was from Ozgrid.


Private Sub Worksheet_Activate()
Dim wSheet As Worksheet
Dim l As Long
l = 1
With Me
.Columns(1).ClearContents
.Cells(1, 1) = "Index"
.Cells(1, 1).Name = "Index"
End With

For Each wSheet In Worksheets
If wSheet.Name <> Me.Name Then
l = l + 1
With wSheet
.Range("A1").Name = "Start_" & wSheet.Index
.Hyperlinks.Add Anchor:=.Range("A1"), Address:="", _
SubAddress:="Index"
Me.Cells(l, 1).Value = wSheet.Name
End With

Me.Hyperlinks.Add Anchor:=Me.Cells(l, 1), Address:="", _
SubAddress:="Start_" & wSheet.Index
Me.Cells(l, 1).Value = wSheet.Name
End If
Next wSheet
End Sub


Thanks

Norie
04-05-2008, 10:28 AM
There's no attachment.:confused:

And I don't think that code would have any problems running in 97.:)

Why not explain in words what you want to do?

ie which cell from the worksheets do you want and where is the destination? new sheet/existing sheet

Simon Lloyd
04-05-2008, 10:47 AM
If you are just looking to find the value adjacent to a value you are looking for across all sheets then maybe this will point you in the right direction:

Sub Find_Value_Copy_Offset()
Dim SH As Worksheet, IB As String, rFound As String
IB = InputBox("Enter value to find")
For Each SH In Sheets
If SH.Name = "Sheet3" Then GoTo Nxt
rFound = SH.Cells.Find(What:=IB, After:=SH.Range("A1"), LookIn:=xlFormulas, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
, SearchFormat:=False).Address
Sheets("Sheet3").Range("A65536").End(xlUp).Offset(1, 0) _
= SH.Range(rFound).Offset(0, 1).Value
Nxt:
Next SH
End Sub

intel122
04-05-2008, 10:51 AM
Thanks for the reply. That is the amended code which does work in 97. Originally, it used TextToDisplay, which didn't work with 97. It was a minor fix, so nothing spectacular, I admit.

Apparently, my post count isn't high enough to post links or attachments. Sigh.

intel122
04-05-2008, 10:53 AM
Simon, thanks, but that is not quite what I am looking for. I will post the link to my workbook when I have posted a few more times.

intel122
04-05-2008, 10:53 AM
Thanks for the replies.

intel122
04-05-2008, 10:55 AM
I shall post the link in my next post. Why is there a limit.

intel122
04-05-2008, 10:56 AM
here is the link to the workbook:

http://www.vbaexpress.com/forum/attachment.php?attachmentid=8349&d=1207416374

thanks

Simon Lloyd
04-05-2008, 11:09 AM
The limit is there i think to prevent automated postings and attachments from such just keeps the forum clean ( i think!)

Simon Lloyd
04-05-2008, 11:15 AM
My code practically did what you need, try this:

Sub Macro1()
Dim SH As Worksheet, rFound As String
Sheets.Add
ActiveSheet.Name = "Trading Summary"
For Each SH In Sheets
If SH.Name = "Trading summary" Then GoTo Nxt
rFound = SH.Cells.Find(What:="Profit", After:=SH.Range("A1"), LookIn:=xlFormulas, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
, SearchFormat:=False).Address
Sheets("Trading Summary").Range("A65536").End(xlUp).Offset(1, 0) _
= SH.Name
Sheets("Trading Summary").Range("A65536").End(xlUp).Offset(0, 2) _
= SH.Range(rFound).Offset(0, 1).Value
Nxt:
Next SH
End Sub

intel122
04-05-2008, 11:19 AM
thanks Simon, I'll try it later.

This is for work, but Andrew Lloyd Webber is looking for a new Nancy on the BBC right now, so priorities must.

Simon Lloyd
04-05-2008, 11:19 AM
Your workbook was completely devoid of code! even though you say the summary sheet was created by code!

Here's your workbook with code added!

Simon Lloyd
04-05-2008, 11:20 AM
Well forgive me but i will be sticking pins in my eyes as it's marginally more interesting!

Norie
04-05-2008, 11:24 AM
Simon

Why are you using Goto?

Simon Lloyd
04-05-2008, 11:28 AM
In my code i forgot about your other sheets so it will error so use this:

Sub Macro1()
Dim SH As Worksheet, IB As String, rFound As String
Sheets.Add
ActiveSheet.Name = "Trading Summary"
For Each SH In Sheets
If SH.Name = "Trading Summary" Or SH.Name = "StartAdj" Or SH.Name = "EndAdj" Then GoTo Nxt
rFound = SH.Cells.Find(What:="Profit", After:=SH.Range("A1"), LookIn:=xlFormulas, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
, SearchFormat:=False).Address
Sheets("Trading Summary").Range("A65536").End(xlUp).Offset(1, 0) _
= SH.Name
Sheets("Trading Summary").Range("A65536").End(xlUp).Offset(0, 2) _
= SH.Range(rFound).Offset(0, 1).Value
Nxt:
Next SH
End Sub

Simon Lloyd
04-05-2008, 11:30 AM
So the Op could follow it easier but here's it without:

Sub Macro1()
Dim SH As Worksheet, IB As String, rFound As String
Sheets.Add
ActiveSheet.Name = "Trading Summary"
For Each SH In Sheets
If SH.Name = "Trading Summary" Or SH.Name = "StartAdj" Or SH.Name = "EndAdj" Then
Else
rFound = SH.Cells.Find(What:="Profit", After:=SH.Range("A1"), LookIn:=xlFormulas, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
, SearchFormat:=False).Address
Sheets("Trading Summary").Range("A65536").End(xlUp).Offset(1, 0) _
= SH.Name
Sheets("Trading Summary").Range("A65536").End(xlUp).Offset(0, 2) _
= SH.Range(rFound).Offset(0, 1).Value
End If
Next SH
End Sub

intel122
04-05-2008, 12:52 PM
Simon, thanks for all your help with this.

I should add that the workbook I attached was a simplified version of what I was trying to do, just to make it clear; otherwise it wouldn't have made any sense.

There is VB in my original file.

intel122
04-05-2008, 01:43 PM
How could I use it if I wanted it to pick up every worksheet that started with "Adj", as the sheets I want it to pick up will be called "adj property", "adj investments", etc.

Thanks again

mdmackillop
04-05-2008, 02:06 PM
Amend this line to check the first three letters of the sheet name

If SH.Name = "Trading Summary" Or SH.Name = "StartAdj" Or SH.Name = "EndAdj" Then

intel122
04-05-2008, 02:10 PM
That was actually my question. How do I amend it so it checks only the first 3 letters of the sheet name, and uses only those sheets?

mdmackillop
04-05-2008, 02:13 PM
If Left(sh.name,3) = "Adj" then

intel122
04-05-2008, 02:41 PM
That wouldn't work because there is an "else" statement in the IF.

But I just got rid of the Else.

Thanks

Simon Lloyd
04-05-2008, 03:15 PM
All you had to do was add to the statement that ignored certain sheets, as for any other sheet it would have worked fine, why supply a workbook or make requests that do not meet your needs, it would have been so much easier to actually supply or ask for what you really needed!

Simon Lloyd
04-05-2008, 03:16 PM
If you have the solution you wanted from this post please mark it Solved by using the thread tools at the top of this page.