Referencing Cells in VB

[Guest]

I am attempting to write a macro that will pull information from a spreadsheet into a list on another sheet. For instance, I have many pieces of equipment and want to see only the equipment for a certain system, so I type in the system I want, hit my button, and go. I am running into problems with searching to the next cell in the spreadsheet. This is the code I have so far

Private Sub CommandButton2_Click()
'Creating Report Number One
t = 1
System = Sheets("input").Range("E3")
Do While t < 10
ActualSystem = Sheets("electrical").Range("At")

If System = ActualSystem Then
Sheets("electrical").Range("At").Copy Destination:= _
Sheets("report").Cells(Rows.Count, 1).End(x1Up) _
.Offset(1, 0)
t = t + 1
Else
t = t + 1

End If
Loop
End Sub

I want it to put only the pieces of equipment for that particular system on the list, and I want to have it where there are no blank spaces between the pieces of equipment on the list. The system is numbered like this:

"Bxx"

with xx being any integers. I know I am referencing the cells wrong in Excel, I know you just cant say Cell (At) and have t be counted each time through, which is the problem I am running into, it gives me a runtime error whenever it gets to the ActualSystem variable. Any help on this matter would be greatly appreciated. Thanks.

Brian

 

[Tom Ogilvy]

I assume there will be multiple cells with B11 for example. If the system is
identified only by the first character being B and could have any two
numbers after, then you will have to modify your equality test to only look
at the first character

If left(System,1) = Left(ActualSystem,1) Then


Private Sub CommandButton2_Click()
'Creating Report Number One
t = 1

System = Sheets("input").Range("E3").Value
Do While t < 10
ActualSystem = Sheets("electrical").Range("At")(t).Value

If System = ActualSystem Then
Sheets("electrical").Range("At")(t).Copy Destination:= _
Sheets("report").Cells(Rows.Count, 1).End(x1Up) _
.Offset(1, 0)
End If
t = t + 1
Loop
End Sub

--
Regards,
Tom Ogilvy

I am attempting to write a macro that will pull information from a

spreadsheet into a list on another sheet. For instance, I have many pieces
of equipment and want to see only the equipment for a certain system, so I
type in the system I want, hit my button, and go. I am running into problems
with searching to the next cell in the spreadsheet. This is the code I have
so far

Private Sub CommandButton2_Click()
'Creating Report Number One
t = 1
System = Sheets("input").Range("E3")
Do While t < 10
ActualSystem = Sheets("electrical").Range("At")

If System = ActualSystem Then
Sheets("electrical").Range("At").Copy Destination:= _
Sheets("report").Cells(Rows.Count, 1).End(x1Up) _
.Offset(1, 0)
t = t + 1
Else
t = t + 1

End If
Loop
End Sub

I want it to put only the pieces of equipment for that particular system

"Bxx"

with xx being any integers. I know I am referencing the cells wrong in

Excel, I know you just cant say Cell (At) and have t be counted each time
through, which is the problem I am running into, it gives me a runtime error
whenever it gets to the ActualSystem variable. Any help on this matter would
be greatly appreciated. Thanks.

 

[Mjschndrt]

Brian,
try this - sub a loops through data to the end & calls sub
2 everytime there is a match.

sub 2 reads the info for system & writes to other data
sheet,

this is pretty simple and should do the job for you.

good luck,

Martin
---------------
Private rw as integer 'Column searching from
Private col as integer 'Column searching to
Private temp as string
Private myvalue as string ' my selection
Private target as string 'sheet write from
Private source as string 'sheet write to
Private mycol as integer ' col in sheet write to

Sub mysub()
rw = 2 ' assume header start in row 2
col= 5 'or whatever col system is referenced
mycol=5 'or whatever
source = "source data sheet"
target = "target data sheet"
Do
temp = Worksheets(source).cells(rw,col).value
if temp= "" then
Exit Do 'EOF
End if
If temp=myvalue Then ' this is what I want
write_target
Endif
rw=rw+1
Loop
End sub

Private sub write_target()
Dim i as integer " row
Dim j as integer 'col
i =2
j = 5

Do
temp= Worksheets(target).cells(i, j ),Value
if temp="" Then " I am @EOList
temp=Worksheets(source).cells(rw, mycol).Value 'this is
what I am getting
Worksheets(target).Cells( i, j).Value ' writing
Exit DO
End if

i=i+1
Loop
End sub

 

[Andrew Mauer]

I am attempting to write a macro that will pull information from a
spreadsheet into a list on another sheet. For instance, I have many
pieces of equipment and want to see only the equipment for a certain
system, so I type in the system I want, hit my button, and go. I am
running into problems with searching to the next cell in the
spreadsheet. This is the code I have so far

Private Sub CommandButton2_Click()
'Creating Report Number One
t = 1
System = Sheets("input").Range("E3")
Do While t < 10
ActualSystem = Sheets("electrical").Range("At")

If System = ActualSystem Then
Sheets("electrical").Range("At").Copy Destination:= _
Sheets("report").Cells(Rows.Count, 1).End(x1Up) _
.Offset(1, 0)
t = t + 1
Else
t = t + 1

End If
Loop
End Sub

I want it to put only the pieces of equipment for that particular
system on the list, and I want to have it where there are no blank
spaces between the pieces of equipment on the list. The system is
numbered like this:

"Bxx"

with xx being any integers. I know I am referencing the cells wrong in
Excel, I know you just cant say Cell (At) and have t be counted each
time through, which is the problem I am running into, it gives me a
runtime error whenever it gets to the ActualSystem variable. Any help
on this matter would be greatly appreciated. Thanks.

Brian

Why don't you try this:

Dim pos As Integer
....
'Begin looping
For pos = 1 to 10
'Since the Index parameter accepts String value, use
'string concatenation to provide Range Index value.0
Sheets("Sheet1").Range("A" & pos).Copy ...
Next pos
[/QUOTE][/QUOTE][/QUOTE]
To reply directly, remove .nospam from address.