expression for the code "cells.find(..)"

**sakura.saki** · 06-14-2012, 07:46 AM

Hi, I am having the following code with the cells.find like this:[vba]Set Source1 = Cells.Find(What:=("Team1"), After:=ActiveCell, LookIn:= _
xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:= _
xlNext, MatchCase:=False, SearchFormat:=False)[/vba]

Team1 is one of the 30 teams I have, the problem is, I have to do it 30 timesbut the rest of code is the same. so I am thinking, if I can make a name list in a sheet and turn the code into something like:
[vba]for i=0 to 29
Set Source1 = Cells.Find(sheets(1).cells(1,1)..offset(i,0).value), After:=ActiveCell, LookIn:= _
xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:= _
xlNext, MatchCase:=False, SearchFormat:=False) ...[/vba]

but somehow there is always an error message, so does anyone knows how to express it, so that the value of a cell in a sheet will be searched?

thanks

**mikerickson** · 06-14-2012, 07:55 AM

Why the double dots? Try[vba]sheets(1).cells(1,1).offset(i,0).value[/vba]

**sakura.saki** · 06-14-2012, 08:06 AM

sorry , it is a typo...thanks.I tried it. and I think this part maybe OK. it was something else which is wrong... the situation is now: the code works fine without the "for next", it means, in a single case it works perfectly, but when I tried with the "for ...next" code, there is a line marked yellow and it says :object variable or with block variable not set...the line in red is the error line..

[VBA][VBA]Sub converse2()
Dim j As Integer
For j = 0 To 27
LastRow = Sheets("ICor").Cells.Find(What:="*", SearchOrder:=xlRows, SearchDirection:=xlPrevious, LookIn:=xlValues).Row
NextEmptyrow = LastRow + 1
Sheets("IToAcc").Select
Cells(1, 1).Activate
Set Source1 = Cells.Find(What:=Sheets("teams list").Cells(1, 1).Offset(j, 0), After:=ActiveCell, LookIn:= _
xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:= _
xlNext, MatchCase:=False, SearchFormat:=False)

Sheets("ICor").Range("A1").Value = Sheets("all teams together").Range("A1").Value
Sheets("ICor").Range("B1").Value = Sheets("all teams together").Range("B1").Value
Sheets("ICor").Range("C1").Value = Sheets("all teams together").Range("C1").Value
Sheets("ICor").Range("D1").Value = Sheets("all teams together").Range("D1").Value
Sheets("ICor").Range("E1").Value = Sheets("all teams together").Range("E1").Value
Sheets("ICor").Range("F1").Value = Sheets("all teams together").Range("F1").Value
Sheets("ICor").Range("G1").Value = Sheets("all teams together").Range("G1").Value
Sheets("ICor").Range("H1").Value = Sheets("all teams together").Range("H1").Value
Sheets("ICor").Range("I1").Value = Sheets("all teams together").Range("I1").Value

Sheets("ICor").Range("C" & NextEmptyrow).Value = Sheets("IToAcc").Cells(2, Source1.Column).Value
Sheets("ICor").Range("D" & NextEmptyrow).Value = Sheets("IToAcc").Cells(4, Source1.Column).Value
Set Source2 = Cells.Find(What:=("Win"), After:=Source1, LookIn:= _
xlValues, LookAt:=xlWhole, SearchOrder:=xlByColumns, SearchDirection:= _
xlDown, MatchCase:=False, SearchFormat:=False)

Set Source3 = Cells.Find(What:=("Lose"), After:=Source2, LookIn:= _
xlValues, LookAt:=xlWhole, SearchOrder:=xlByColumns, SearchDirection:= _
xlDown, MatchCase:=False, SearchFormat:=False)

If Source3.Row - Source2.Row >= 2 Then

For i = 1 To Source3.Row - Source2.Row - 1
Sheets("ICor").Range("E" & NextEmptyrow).Offset(i - 1, 0).Value = Sheets("IToAcc").Cells(Source2.Row + 1, Source2.Column).Offset(i - 1, 0).Value
Next i
End If

If Source3.Row - Source2.Row < 2 Then
Sheets("IToAcc").Range("E" & NextEmptyrow).Offset(i - 1, 0).Value = ""
End If

Set Source4 = Cells.Find(What:=("PIP"), After:=Source3, LookIn:= _
xlValues, LookAt:=xlWhole, SearchOrder:=xlByColumns, SearchDirection:= _
xlDown, MatchCase:=False, SearchFormat:=False)

If Source4.Row - Source3.Row >= 2 Then
For i = 1 To Source4.Row - Source3.Row - 1
Sheets("ICor").Range("F" & NextEmptyrow).Offset(i - 1, 0).Value = Sheets("IToAcc").Cells(Source3.Row + 1, Source3.Column).Offset(i - 1, 0).Value
Next i
End If

If Source4.Row - Source3.Row < 2 Then
Sheets("ICor").Range("F" & NextEmptyrow).Offset(i - 1, 0).Value = ""
End If

Set Source5 = Cells.Find(What:=("Attention"), After:=Source4, LookIn:= _
xlValues, LookAt:=xlWhole, SearchOrder:=xlByColumns, SearchDirection:= _
xlDown, MatchCase:=False, SearchFormat:=False)

If Source5.Row - Source4.Row >= 2 Then
For i = 1 To Source5.Row - Source4.Row - 1
Sheets("ICor").Range("F" & NextEmptyrow).Offset(i - 1, 0).Value = Sheets("IToAcc").Cells(Source4.Row + 1, Source4.Column).Offset(i - 1, 0).Value
Next i
End If

If Source5.Row - Source4.Row < 2 Then
Sheets("ICor").Range("G" & NextEmptyrow).Offset(i - 1, 0).Value = ""
End If

Set Source6 = Cells.Find(What:=("Activities"), After:=Source5, LookIn:= _
xlValues, LookAt:=xlWhole, SearchOrder:=xlByColumns, SearchDirection:= _
xlDown, MatchCase:=False, SearchFormat:=False)

If Source6.Row - Source5.Row >= 2 Then
For i = 1 To Source6.Row - Source5.Row - 1
Sheets("ICor").Range("H" & NextEmptyrow).Offset(i - 1, 0).Value = Sheets("IToAcc").Cells(Source5.Row + 1, Source5.Column).Offset(i - 1, 0).Value
Next i
End If

If Source6.Row - Source5.Row < 2 Then
Sheets("ICor").Range("H" & NextEmptyrow).Offset(i - 1, 0).Value = ""
End If

Set Source7 = Cells.Find(What:=(""), After:=Source6, LookIn:= _
xlValues, LookAt:=xlWhole, SearchOrder:=xlByColumns, SearchDirection:= _
xlDown, MatchCase:=False, SearchFormat:=False)

If Source7.Row - Source6.Row >= 2 Then
For i = 1 To Source7.Row - Source6.Row - 1
Sheets("ICor").Range("I" & NextEmptyrow).Offset(i - 1, 0).Value = Sheets("IToAcc").Cells(Source6.Row + 1, Source6.Column).Offset(i - 1, 0).Value
Next i
End If

If Source7.Row - Source6.Row < 2 Then
Sheets("ICor").Range("I" & NextEmptyrow).Offset(i - 1, 0).Value = ""
End If

LastRow = Sheets("ICor").Cells.Find(What:="*", SearchOrder:=xlRows, SearchDirection:=xlPrevious, LookIn:=xlValues).Row
For i = 0 To LastRow - Range("C" & NextEmptyrow).Row
Sheets("ICor").Range("C" & NextEmptyrow).Offset(i, 0).Value = Sheets("IToAcc").Cells(Source1.Row, Source1.Column).Value
Next i
Next j
End Sub

[/VBA]

**CodeNinja** · 06-14-2012, 08:21 AM

I think you are trying to cast a range into a cell...

You might try:
Sheets("ICor").Range("C" & NextEmptyrow).Value = Sheets("IToAcc").range(split(source1.address, "$") (1) & 2).Value

Although I didn't test it... as you didn't give spreadsheet to validate against...

**sakura.saki** · 06-14-2012, 08:33 AM

thanks for the help. but I just checked it up. it is not the problem...because: without the "For next" code it runs just fine...just the way it is, nothing seems to be wrong.but when I tried to use the variable j, the error messege appears...

**CodeNinja** · 06-14-2012, 08:40 AM

post a sample excel sheet (does not have to be actual data) so I can take a look at it...

Thanks,

**sakura.saki** · 06-15-2012, 05:57 AM

sorry...but how can I do it? I serached the board, and according to the faq, there should be a button at the bottom of the window to upload attachments, but I can´t find it.

**Tinbendr** · 06-15-2012, 06:44 AM

Right here.

**sakura.saki** · 06-15-2012, 08:43 AM

Originally Posted by Tinbendr

Right here.

thanks. here a sample of my excel sheet as attachment, and what I am trying to do, is to change the table on sheet 2 into the one in sheet 3.
there are some titles like: deals won/lost on sheet 3. they are corresponding to the contents in each column on sheet2. the problem is, the contents under these titles are from a userform and the length is not certain. the length of the content depend on the input from the userform. max. 10 for each title.
there is my code for doing so:[VBA]Sub converse2()
Sheets("Sheet1").Select
Cells(1, 1).Activate

LastRow = Sheets("Sheet2").Cells.Find(What:="*", SearchOrder:=xlRows, SearchDirection:=xlPrevious, LookIn:=xlValues).Row
nextemptyrow = LastRow + 1
Sheets("Sheet1").Select
Cells(1, 1).Activate
Set Source1 = Cells.Find(What:=("Asia1"), After:=ActiveCell, LookIn:= _
xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:= _
xlNext, MatchCase:=False, SearchFormat:=False)

Sheets("Sheet2").Range("A1").Value = Sheets("all teams together").Range("A1").Value
Sheets("Sheet2").Range("B1").Value = Sheets("all teams together").Range("B1").Value
Sheets("Sheet2").Range("C1").Value = Sheets("all teams together").Range("C1").Value
Sheets("Sheet2").Range("D1").Value = Sheets("all teams together").Range("D1").Value
Sheets("Sheet2").Range("E1").Value = Sheets("all teams together").Range("E1").Value
Sheets("Sheet2").Range("F1").Value = Sheets("all teams together").Range("F1").Value
Sheets("Sheet2").Range("G1").Value = Sheets("all teams together").Range("G1").Value
Sheets("Sheet2").Range("H1").Value = Sheets("all teams together").Range("H1").Value
Sheets("Sheet2").Range("I1").Value = Sheets("all teams together").Range("I1").Value

Sheets("Sheet2").Range("C" & nextemptyrow).Value = Sheets("Sheet1").Cells(Source1.Row, Source1.Column).Value
Sheets("Sheet2").Range("D" & nextemptyrow).Value = Sheets("Sheet1").Cells(Source1.Row + 2, Source1.Column).Value
Set Source2 = Cells.Find(What:=("Deals Won"), After:=Source1, LookIn:= _
xlValues, LookAt:=xlWhole, SearchOrder:=xlByColumns, SearchDirection:= _
xlDown, MatchCase:=False, SearchFormat:=False)

Set Source3 = Cells.Find(What:=("Deals Lost"), After:=Source2, LookIn:= _
xlValues, LookAt:=xlWhole, SearchOrder:=xlByColumns, SearchDirection:= _
xlDown, MatchCase:=False, SearchFormat:=False)

If Source3.Row - Source2.Row >= 2 Then

For i = 1 To Source3.Row - Source2.Row - 1
Sheets("Sheet2").Range("E" & nextemptyrow).Offset(i - 1, 0).Value = Sheets("Sheet1").Cells(Source2.Row + 1, Source2.Column).Offset(i - 1, 0).Value
Next i
End If

If Source3.Row - Source2.Row < 2 Then
Sheets("Sheet1").Range("E" & nextemptyrow).Offset(i - 1, 0).Value = ""
End If

Set Source4 = Cells.Find(What:=("Deal Pipeline"), After:=Source3, LookIn:= _
xlValues, LookAt:=xlWhole, SearchOrder:=xlByColumns, SearchDirection:= _
xlDown, MatchCase:=False, SearchFormat:=False)

If Source4.Row - Source3.Row >= 2 Then
For i = 1 To Source4.Row - Source3.Row - 1
Sheets("Sheet2").Range("F" & nextemptyrow).Offset(i - 1, 0).Value = Sheets("Sheet1").Cells(Source3.Row + 1, Source3.Column).Offset(i - 1, 0).Value
Next i
End If

If Source4.Row - Source3.Row < 2 Then
Sheets("Sheet2").Range("F" & nextemptyrow).Offset(i - 1, 0).Value = ""
End If

Set Source5 = Cells.Find(What:=("Management Attention"), After:=Source4, LookIn:= _
xlValues, LookAt:=xlWhole, SearchOrder:=xlByColumns, SearchDirection:= _
xlDown, MatchCase:=False, SearchFormat:=False)

If Source5.Row - Source4.Row >= 2 Then
For i = 1 To Source5.Row - Source4.Row - 1
Sheets("Sheet2").Range("F" & nextemptyrow).Offset(i - 1, 0).Value = Sheets("Sheet1").Cells(Source4.Row + 1, Source4.Column).Offset(i - 1, 0).Value
Next i
End If

If Source5.Row - Source4.Row < 2 Then
Sheets("Sheet2").Range("G" & nextemptyrow).Offset(i - 1, 0).Value = ""
End If

Set Source6 = Cells.Find(What:=("Activities"), After:=Source5, LookIn:= _
xlValues, LookAt:=xlWhole, SearchOrder:=xlByColumns, SearchDirection:= _
xlDown, MatchCase:=False, SearchFormat:=False)

If Source6.Row - Source5.Row >= 2 Then
For i = 1 To Source6.Row - Source5.Row - 1
Sheets("Sheet2").Range("H" & nextemptyrow).Offset(i - 1, 0).Value = Sheets("Sheet1").Cells(Source5.Row + 1, Source5.Column).Offset(i - 1, 0).Value
Next i
End If

If Source6.Row - Source5.Row < 2 Then
Sheets("Sheet2").Range("H" & nextemptyrow).Offset(i - 1, 0).Value = ""
End If

Set Source7 = Cells.Find(What:=(""), After:=Source6, LookIn:= _
xlValues, LookAt:=xlWhole, SearchOrder:=xlByColumns, SearchDirection:= _
xlDown, MatchCase:=False, SearchFormat:=False)

If Source7.Row - Source6.Row >= 2 Then
For i = 1 To Source7.Row - Source6.Row - 1
Sheets("Sheet2").Range("I" & nextemptyrow).Offset(i - 1, 0).Value = Sheets("Sheet1").Cells(Source6.Row + 1, Source6.Column).Offset(i - 1, 0).Value
Next i
End If

If Source7.Row - Source6.Row < 2 Then
Sheets("Sheet2").Range("I" & nextemptyrow).Offset(i - 1, 0).Value = ""
End If

LastRow = Sheets("Sheet2").Cells.Find(What:="*", SearchOrder:=xlRows, SearchDirection:=xlPrevious, LookIn:=xlValues).Row
For i = 0 To LastRow - Range("C" & nextemptyrow).Row
Sheets("Sheet2").Range("C" & nextemptyrow).Offset(i, 0).Value = Sheets("Sheet1").Cells(Source1.Row, Source1.Column).Value
Next i

End Sub[/VBA]

**CodeNinja** · 06-15-2012, 01:05 PM

There is no code attached in that sample, so I can not play with existing code. Please supply sample WITH code.

Thanks,

**sakura.saki** · 06-20-2012, 07:57 AM

Originally Posted by CodeNinja

There is no code attached in that sample, so I can not play with existing code. Please supply sample WITH code.

Thanks,

thank you! here the worksheet. it is a similar code (not the one as above) trying to do a similar thing, but somehow when I try to make it run with the for next code, it doens´t work so well as I wrote 21 seperate codes for each teams with the exact names...so it has the same problem as the one above.

I wrote a code to do it, this time it doesn´t come with a error messege, but the result is still not what I want...you can have a try and you will understand what I mean...I think the problem may be in the cells.find code, but you are the expert.

**CodeNinja** · 06-20-2012, 12:33 PM

Sakura.Saki,
As you said, this code is not throwing an error. What exactly do you want the code to do that it is not already doing?

**sakura.saki** · 06-21-2012, 01:00 AM

the problem is, it is not providing the right outcome either... I don´t know if you noticed, after the first column, the other columns of the output are exactly the same which is not right. it should contain the corresponding contents, just the form should change. basically, I try to change the form of the tables because I have to export them into word, and I managed to do so by writing about 30 codes for each team, but I would like to do so, so that I don´t have to write so much code, it is really stupid and difficult to change when the teams change, so I come with the idea, to put all team names into a list on a sheet, and let it search according to the list to find the right list name.but I can do it somehow, either with error masseage, and the source can not be found, shown as "Nothing", or like this one, which is wrong...

**CodeNinja** · 06-21-2012, 06:10 AM

Sakura.Saki,
Please supply a sheet showing what you want the results to look like when you are done running the macro. This will be a tremendous help in determining exactly what you want the macro to do.

Thanks.

**sakura.saki** · 06-21-2012, 06:25 AM

Originally Posted by CodeNinja

Sakura.Saki,
Please supply a sheet showing what you want the results to look like when you are done running the macro. This will be a tremendous help in determining exactly what you want the macro to do.

Thanks.

Hi. sorry, I thought I had it in the attachment, but it is weird...last time I ran the code, it did provide something, and this time i run it again, there is nothing happening, although I didn´t change anything... I made a sample on tab "word" to show how it should look like. basically, I would like to make the table on tab "teams CW" in the way it is on tab "word", on tab "word" there are about 4 teams as an example, it should be exactly so many teams as on tab "team CW". I hope this time it is a bit clear...

**CodeNinja** · 06-21-2012, 07:58 AM

Sakura.Saki,
Not the cleanest code I have ever written, but I don't have a lot of time today... This should be a good start for you...

Good luck

[VBA]
Sub wordloop2()
Dim rng As Range
Dim iFirstRow As Integer
Dim iLastRow As Integer
Dim shtT As Worksheet
Dim shtW As Worksheet
Dim j As Integer
Dim i As Integer
Dim iTeams As Integer
Dim iRow As Integer

' this will allow you to add more or less than 21 teams dynamically
' you will just have to add them to the data sheet as well as the teams CW sheet.
iTeams = Sheets("Data").Range("A65536").End(xlUp).Row - 1

Set rng = Sheets("teams CW").UsedRange
iFirstRow = 2
Set shtT = Sheets("teams CW")
Set shtW = Sheets("word")

For i = 1 To iTeams
'find first row with team(i)
While shtT.Cells(iFirstRow, 3) <> Sheets("Data").Cells(i + 1, 1)
iFirstRow = iFirstRow + 1
Wend
'Fiind the last row with team(i)
iLastRow = iFirstRow
While shtT.Cells(iLastRow, 3) = Sheets("data").Cells(i + 1, 1)
iLastRow = iLastRow + 1
Wend
iLastRow = iLastRow - 1

' fill data in column i
With shtW
.Cells(1, i) = shtT.Cells(1, 3) 'header team name
.Cells(2, i) = Sheets("data").Cells(i + 1, 1) ' fill team name
.Cells(3, i) = shtT.Cells(1, 4) ' header Production Credits
.Cells(4, i) = shtT.Cells(iFirstRow, 4) 'fill production credits
.Cells(5, i) = shtT.Cells(1, 5) ' header deals won
shtT.Range("E" & iFirstRow & ":E" & iLastRow).Copy ' fill deals won
.Activate
.Cells(6, i).Select
Selection.PasteSpecial
iRow = 7 + iLastRow - iFirstRow 'set next row
.Cells(iRow, i) = shtT.Cells(1, 6) 'header deals lost
shtT.Range("F" & iFirstRow & ":F" & iLastRow).Copy 'fill deals lost
.Cells(iRow + 1, i).Select
Selection.PasteSpecial
iRow = iRow + 1 + iLastRow - iFirstRow 'set next row
.Cells(iRow, i) = shtT.Cells(1, 7) 'header pipeline
shtT.Range("G" & iFirstRow & ":G" & iLastRow).Copy 'fill pipeline
.Cells(iRow + 1, i).Select
Selection.PasteSpecial
iRow = iRow + 1 + iLastRow - iFirstRow 'set next row
.Cells(iRow, i) = shtT.Cells(1, 8) 'header attention
shtT.Range("H" & iFirstRow & ":H" & iLastRow).Copy 'fill attention
.Cells(iRow + 1, i).Select
Selection.PasteSpecial
End With

Next i

End Sub
[/VBA]

Thread: expression for the code "cells.find(..)"

Thread Tools

Display

expression for the code "cells.find(..)"

Posting Permissions