Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Referencing Cells in VB
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Referencing Cells in VB
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Referencing Cells in VB
"?B?QnJpYW4gTWNHdWlyZQ==?=" wrote in
: 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 -- Andrew Mauer To reply directly, remove .nospam from address. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
referencing cells based on conditions in other cells | Excel Discussion (Misc queries) | |||
Referencing several cells with IF? | Excel Worksheet Functions | |||
Referencing Cells | Excel Worksheet Functions | |||
Referencing other cells | Excel Discussion (Misc queries) | |||
Referencing Cells | Excel Worksheet Functions |