Referencing Cells in VB
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
-----Original Message-----
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
"Brian McGuire" wrote in
message
...
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
.
|