Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Really Help with existing VBA code...
Hello,
I have the following existing code which look at a column "I" and if the word "ASC" text is present in the cell, then skip filling the data in column I thru N for the row. What I would like to change the code so that it will fill the data in the cell from column I thru N that does not have "ASC" on the cell. Thanks Old code sample: A I J K L M N Line# 1300 1300 1500 1500 1700 1700 001 ASC 002 x x x x x x 003 x x x x x x skip row 2 with line# 001 cause ASC is in column I2 so skipping cell J2 thru N2). Where x is data that the macro fill in. New code wanted: A I J K L M N Line# 1300 1300 1500 1500 1700 1700 001 ASC x ASC x x x 002 x ASC x x x x 003 x x x x x x it does not skip row 2 with line# 001, but rather fill in the missing cell (J2, L2, M2 and N2) without "ASC" in the cell. Here is my existing code: Private Sub Macro() ' Dim R1300M100(10000, 3) Dim R1300M200(10000, 3) Dim R1300M300(10000, 3) Dim R1500M100(10000, 3) Dim R1500M200(10000, 3) Dim R1500M300(10000, 3) Dim R1700M100(10000, 3) Dim R1700M200(10000, 3) Dim R1700M300(10000, 3) Dim R1100M100(10000, 3) Dim R1100M200(10000, 3) Dim R1100M300(10000, 3) With Sheets("100") LastRowSh1 = _ .Cells(Rows.Count, "I").End(xlUp).Row If LastRowSh1 < 1 Then Set ColIRange = .Range(.Cells(2, "I"), _ .Cells(LastRowSh1, "I")) For Each Cell In ColIRange If (Cell < "ASC") And _ (.Rows(Cell.Row).Hidden = False) Then .Range("H" & Cell.Row & ":X" & Cell.Row).ClearContents End If Next Cell End If End With With Sheets("200") LastRowSh2 = _ .Cells(Rows.Count, "I").End(xlUp).Row If LastRowSh2 < 1 Then Set ColIRange = .Range(.Cells(2, "I"), _ .Cells(LastRowSh2, "I")) For Each Cell In ColIRange If (Cell < "ASC") And _ (.Rows(Cell.Row).Hidden = False) Then .Range("H" & Cell.Row & ":X" & Cell.Row).ClearContents End If Next Cell End If End With With Sheets("300") LastRowSh3 = _ .Cells(Rows.Count, "I").End(xlUp).Row If LastRowSh3 < 1 Then Set ColIRange = .Range(.Cells(2, "I"), _ .Cells(LastRowSh3, "I")) For Each Cell In ColIRange If (Cell < "ASC") And _ (.Rows(Cell.Row).Hidden = False) Then .Range("H" & Cell.Row & ":X" & Cell.Row).ClearContents End If Next Cell End If End With LastRowSh4 = Sheets("Data"). _ Cells(Rows.Count, "A").End(xlUp).Row More code to insert data, etc..... |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Really Help with existing VBA code...
I'm looking at your code and I don't think it does what you think it does. I
understood you to say that on each row where "ASC" is in col I it skips filling in data for cols I thru N. What this code actually seems to do is check I and if it isn't "ASC" it CLEARS the contents of H thru X. I haven't examined the same sections for Sheets("200") and Sheets("300") but they seem to be similar. Seems to me this needs to be straightened out before we can continue. --- "Cam" wrote: I have the following existing code which look at a column "I" and if the word "ASC" text is present in the cell, then skip filling the data in column I thru N for the row. What I would like to change the code so that it will fill the data in the cell from column I thru N that does not have "ASC" on the cell. Old code sample: A I J K L M N Line# 1300 1300 1500 1500 1700 1700 001 ASC 002 x x x x x x 003 x x x x x x skip row 2 with line# 001 cause ASC is in column I2 so skipping cell J2 thru N2). Where x is data that the macro fill in. New code wanted: A I J K L M N Line# 1300 1300 1500 1500 1700 1700 001 ASC x ASC x x x 002 x ASC x x x x 003 x x x x x x it does not skip row 2 with line# 001, but rather fill in the missing cell (J2, L2, M2 and N2) without "ASC" in the cell. Here is my existing code: Private Sub Macro() ' Dim R1300M100(10000, 3) Dim R1300M200(10000, 3) Dim R1300M300(10000, 3) Dim R1500M100(10000, 3) Dim R1500M200(10000, 3) Dim R1500M300(10000, 3) Dim R1700M100(10000, 3) Dim R1700M200(10000, 3) Dim R1700M300(10000, 3) Dim R1100M100(10000, 3) Dim R1100M200(10000, 3) Dim R1100M300(10000, 3) With Sheets("100") LastRowSh1 = _ .Cells(Rows.Count, "I").End(xlUp).Row If LastRowSh1 < 1 Then Set ColIRange = .Range(.Cells(2, "I"), _ .Cells(LastRowSh1, "I")) For Each Cell In ColIRange If (Cell < "ASC") And _ (.Rows(Cell.Row).Hidden = False) Then .Range("H" & Cell.Row & ":X" & Cell.Row).ClearContents End If Next Cell End If End With With Sheets("200") LastRowSh2 = _ .Cells(Rows.Count, "I").End(xlUp).Row If LastRowSh2 < 1 Then Set ColIRange = .Range(.Cells(2, "I"), _ .Cells(LastRowSh2, "I")) For Each Cell In ColIRange If (Cell < "ASC") And _ (.Rows(Cell.Row).Hidden = False) Then .Range("H" & Cell.Row & ":X" & Cell.Row).ClearContents End If Next Cell End If End With With Sheets("300") LastRowSh3 = _ .Cells(Rows.Count, "I").End(xlUp).Row If LastRowSh3 < 1 Then Set ColIRange = .Range(.Cells(2, "I"), _ .Cells(LastRowSh3, "I")) For Each Cell In ColIRange If (Cell < "ASC") And _ (.Rows(Cell.Row).Hidden = False) Then .Range("H" & Cell.Row & ":X" & Cell.Row).ClearContents End If Next Cell End If End With LastRowSh4 = Sheets("Data"). _ Cells(Rows.Count, "A").End(xlUp).Row More code to insert data, etc..... |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Really Help with existing VBA code...
Bob,
Thanks for the response. You are right, I forgot. The inserting data part of the macro is below. Please let me know if this is still unclear. Sub InsertData(ByRef MyArray() As Variant, _ Count, Ref, Model, InsertSheet) With Sheets(InsertSheet) RowCount = 2 MyOffset = 0 Do While (Not IsEmpty(.Cells(RowCount, "I")) And _ (.Cells(RowCount, "H") < Model)) Or _ (.Cells(RowCount, "I") = "ASC") Or _ (.Rows(RowCount).Hidden = True) RowCount = RowCount + 1 Loop For LoopCount = 0 To (Count - 1) .Cells(RowCount, "I"). _ Offset(0, (2 * Ref) + MyOffset) = _ MyArray(LoopCount, SO) .Cells(RowCount, "Q"). _ Offset(0, (2 * Ref) + MyOffset) = _ MyArray(LoopCount, OP) If MyOffset = 0 Then .Cells(RowCount, "H").Value = Model MyOffset = 1 Else RowCount = RowCount + 1 Do While (Not IsEmpty(.Cells(RowCount, "I")) And _ (.Cells(RowCount, "H") < Model)) Or _ (.Cells(RowCount, "I") = "ASC") Or _ (.Rows(RowCount).Hidden = True) RowCount = RowCount + 1 Loop MyOffset = 0 End If Next LoopCount End With "Bob Bridges" wrote: I'm looking at your code and I don't think it does what you think it does. I understood you to say that on each row where "ASC" is in col I it skips filling in data for cols I thru N. What this code actually seems to do is check I and if it isn't "ASC" it CLEARS the contents of H thru X. I haven't examined the same sections for Sheets("200") and Sheets("300") but they seem to be similar. Seems to me this needs to be straightened out before we can continue. --- "Cam" wrote: I have the following existing code which look at a column "I" and if the word "ASC" text is present in the cell, then skip filling the data in column I thru N for the row. What I would like to change the code so that it will fill the data in the cell from column I thru N that does not have "ASC" on the cell. Old code sample: A I J K L M N Line# 1300 1300 1500 1500 1700 1700 001 ASC 002 x x x x x x 003 x x x x x x skip row 2 with line# 001 cause ASC is in column I2 so skipping cell J2 thru N2). Where x is data that the macro fill in. New code wanted: A I J K L M N Line# 1300 1300 1500 1500 1700 1700 001 ASC x ASC x x x 002 x ASC x x x x 003 x x x x x x it does not skip row 2 with line# 001, but rather fill in the missing cell (J2, L2, M2 and N2) without "ASC" in the cell. Here is my existing code: Private Sub Macro() ' Dim R1300M100(10000, 3) Dim R1300M200(10000, 3) Dim R1300M300(10000, 3) Dim R1500M100(10000, 3) Dim R1500M200(10000, 3) Dim R1500M300(10000, 3) Dim R1700M100(10000, 3) Dim R1700M200(10000, 3) Dim R1700M300(10000, 3) Dim R1100M100(10000, 3) Dim R1100M200(10000, 3) Dim R1100M300(10000, 3) With Sheets("100") LastRowSh1 = _ .Cells(Rows.Count, "I").End(xlUp).Row If LastRowSh1 < 1 Then Set ColIRange = .Range(.Cells(2, "I"), _ .Cells(LastRowSh1, "I")) For Each Cell In ColIRange If (Cell < "ASC") And _ (.Rows(Cell.Row).Hidden = False) Then .Range("H" & Cell.Row & ":X" & Cell.Row).ClearContents End If Next Cell End If End With With Sheets("200") LastRowSh2 = _ .Cells(Rows.Count, "I").End(xlUp).Row If LastRowSh2 < 1 Then Set ColIRange = .Range(.Cells(2, "I"), _ .Cells(LastRowSh2, "I")) For Each Cell In ColIRange If (Cell < "ASC") And _ (.Rows(Cell.Row).Hidden = False) Then .Range("H" & Cell.Row & ":X" & Cell.Row).ClearContents End If Next Cell End If End With With Sheets("300") LastRowSh3 = _ .Cells(Rows.Count, "I").End(xlUp).Row If LastRowSh3 < 1 Then Set ColIRange = .Range(.Cells(2, "I"), _ .Cells(LastRowSh3, "I")) For Each Cell In ColIRange If (Cell < "ASC") And _ (.Rows(Cell.Row).Hidden = False) Then .Range("H" & Cell.Row & ":X" & Cell.Row).ClearContents End If Next Cell End If End With LastRowSh4 = Sheets("Data"). _ Cells(Rows.Count, "A").End(xlUp).Row More code to insert data, etc..... |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Really Help with existing VBA code...
Well, I'm starting over with this code, and I may not understand it
correctly. But it seems to find the first eligible row, and to start filling in data from an array, looking for the next eligible row after filling in four columns and looping as many times as is specified in the call from the main routine. Right so far? Now, one of the conditions that make a row ineligible is that it have "ASC" in column I, and I guess you're saying that should no longer prevent your program from filling in data in that column; instead you want to allow "ASC" to be in any of the four cells you're about to populate from your array in order to prevent that particular cell from being filled in. Is that right? If so, then I guess it's clear enough that you want to remove that condition about "ASC" from the logic that finds the next eligible row. Then before populating each cell, I guess you want to check to be sure the CELL doesn't contain "ASC", maybe like this: CellOffset = 2 * Ref + MyOffset Set co = .Cells(RowCount, "I").Offset(0, CellOffset) If co.Value < "ASC" then co.Value = MyArray(LoopCount, SO) Set co = .Cells(RowCount, "Q").Offset(0, CellOffset) If co.Value < "ASC" then co.Value = MyArray(LoopCount, OP) I'd probably try to make it a subroutine to avoid the repetition, but this oughta be on the right track. One thing I notice: This logic is perfectly willing to skip over some MyArray values, skipping array cells or even whole rows if their corresponding cells contain "ASC". But perhaps you want every value in the array to be used, or at least every row? That is, if all four target cells in a row have "ASC" you want that data from MyArray to be held for the row after it instead? --- "Cam" wrote: Thanks for the response. You are right, I forgot. The inserting data part of the macro is below. Please let me know if this is still unclear. Sub InsertData(ByRef MyArray() As Variant, _ Count, Ref, Model, InsertSheet) With Sheets(InsertSheet) RowCount = 2 MyOffset = 0 Do While (Not IsEmpty(.Cells(RowCount, "I")) And _ (.Cells(RowCount, "H") < Model)) Or _ (.Cells(RowCount, "I") = "ASC") Or _ (.Rows(RowCount).Hidden = True) RowCount = RowCount + 1 Loop For LoopCount = 0 To (Count - 1) .Cells(RowCount, "I"). _ Offset(0, (2 * Ref) + MyOffset) = _ MyArray(LoopCount, SO) .Cells(RowCount, "Q"). _ Offset(0, (2 * Ref) + MyOffset) = _ MyArray(LoopCount, OP) If MyOffset = 0 Then .Cells(RowCount, "H").Value = Model MyOffset = 1 Else RowCount = RowCount + 1 Do While (Not IsEmpty(.Cells(RowCount, "I")) And _ (.Cells(RowCount, "H") < Model)) Or _ (.Cells(RowCount, "I") = "ASC") Or _ (.Rows(RowCount).Hidden = True) RowCount = RowCount + 1 Loop MyOffset = 0 End If Next LoopCount End With --- "Bob Bridges" wrote: I'm looking at your code and I don't think it does what you think it does. I understood you to say that on each row where "ASC" is in col I it skips filling in data for cols I thru N. What this code actually seems to do is check I and if it isn't "ASC" it CLEARS the contents of H thru X. I haven't examined the same sections for Sheets("200") and Sheets("300") but they seem to be similar. Seems to me this needs to be straightened out before we can continue. --- "Cam" wrote: I have the following existing code which look at a column "I" and if the word "ASC" text is present in the cell, then skip filling the data in column I thru N for the row. What I would like to change the code so that it will fill the data in the cell from column I thru N that does not have "ASC" on the cell. Old code sample: A I J K L M N Line# 1300 1300 1500 1500 1700 1700 001 ASC 002 x x x x x x 003 x x x x x x skip row 2 with line# 001 cause ASC is in column I2 so skipping cell J2 thru N2). Where x is data that the macro fill in. New code wanted: A I J K L M N Line# 1300 1300 1500 1500 1700 1700 001 ASC x ASC x x x 002 x ASC x x x x 003 x x x x x x it does not skip row 2 with line# 001, but rather fill in the missing cell (J2, L2, M2 and N2) without "ASC" in the cell. Here is my existing code: Private Sub Macro() ' Dim R1300M100(10000, 3) Dim R1300M200(10000, 3) Dim R1300M300(10000, 3) Dim R1500M100(10000, 3) Dim R1500M200(10000, 3) Dim R1500M300(10000, 3) Dim R1700M100(10000, 3) Dim R1700M200(10000, 3) Dim R1700M300(10000, 3) Dim R1100M100(10000, 3) Dim R1100M200(10000, 3) Dim R1100M300(10000, 3) With Sheets("100") LastRowSh1 = _ .Cells(Rows.Count, "I").End(xlUp).Row If LastRowSh1 < 1 Then Set ColIRange = .Range(.Cells(2, "I"), _ .Cells(LastRowSh1, "I")) For Each Cell In ColIRange If (Cell < "ASC") And _ (.Rows(Cell.Row).Hidden = False) Then .Range("H" & Cell.Row & ":X" & Cell.Row).ClearContents End If Next Cell End If End With With Sheets("200") LastRowSh2 = _ .Cells(Rows.Count, "I").End(xlUp).Row If LastRowSh2 < 1 Then Set ColIRange = .Range(.Cells(2, "I"), _ .Cells(LastRowSh2, "I")) For Each Cell In ColIRange If (Cell < "ASC") And _ (.Rows(Cell.Row).Hidden = False) Then .Range("H" & Cell.Row & ":X" & Cell.Row).ClearContents End If Next Cell End If End With With Sheets("300") LastRowSh3 = _ .Cells(Rows.Count, "I").End(xlUp).Row If LastRowSh3 < 1 Then Set ColIRange = .Range(.Cells(2, "I"), _ .Cells(LastRowSh3, "I")) For Each Cell In ColIRange If (Cell < "ASC") And _ (.Rows(Cell.Row).Hidden = False) Then .Range("H" & Cell.Row & ":X" & Cell.Row).ClearContents End If Next Cell End If End With LastRowSh4 = Sheets("Data"). _ Cells(Rows.Count, "A").End(xlUp).Row More code to insert data, etc..... |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Really Help with existing VBA code...
Bob,
Thanks again. But I am not sure where to put the code you sugguested in my existing code. There are actually 6 columns of cell in each row to fill (2 each of for 1300, 1500 and 1700), not 4. My existing code looks at whether it is a 100, 200 or 300 model, then start filling in data, but skip the whole rows with the following condition, if it is hidden or column "I" equal "ASC". The new code I want is I don't want to skip the whole row if a row in I column equal ASC, just skip only cells with ASC in it. Let me know if I am still unclear. With Sheets(InsertSheet) RowCount = 2 MyOffset = 0 Do While (Not IsEmpty(.Cells(RowCount, "I")) And _ (.Cells(RowCount, "H") < Model)) Or _ (.Cells(RowCount, "I") = "ASC") Or _ (.Rows(RowCount).Hidden = True) RowCount = RowCount + 1 Loop "Bob Bridges" wrote: Well, I'm starting over with this code, and I may not understand it correctly. But it seems to find the first eligible row, and to start filling in data from an array, looking for the next eligible row after filling in four columns and looping as many times as is specified in the call from the main routine. Right so far? Now, one of the conditions that make a row ineligible is that it have "ASC" in column I, and I guess you're saying that should no longer prevent your program from filling in data in that column; instead you want to allow "ASC" to be in any of the four cells you're about to populate from your array in order to prevent that particular cell from being filled in. Is that right? If so, then I guess it's clear enough that you want to remove that condition about "ASC" from the logic that finds the next eligible row. Then before populating each cell, I guess you want to check to be sure the CELL doesn't contain "ASC", maybe like this: CellOffset = 2 * Ref + MyOffset Set co = .Cells(RowCount, "I").Offset(0, CellOffset) If co.Value < "ASC" then co.Value = MyArray(LoopCount, SO) Set co = .Cells(RowCount, "Q").Offset(0, CellOffset) If co.Value < "ASC" then co.Value = MyArray(LoopCount, OP) I'd probably try to make it a subroutine to avoid the repetition, but this oughta be on the right track. One thing I notice: This logic is perfectly willing to skip over some MyArray values, skipping array cells or even whole rows if their corresponding cells contain "ASC". But perhaps you want every value in the array to be used, or at least every row? That is, if all four target cells in a row have "ASC" you want that data from MyArray to be held for the row after it instead? --- "Cam" wrote: Thanks for the response. You are right, I forgot. The inserting data part of the macro is below. Please let me know if this is still unclear. Sub InsertData(ByRef MyArray() As Variant, _ Count, Ref, Model, InsertSheet) With Sheets(InsertSheet) RowCount = 2 MyOffset = 0 Do While (Not IsEmpty(.Cells(RowCount, "I")) And _ (.Cells(RowCount, "H") < Model)) Or _ (.Cells(RowCount, "I") = "ASC") Or _ (.Rows(RowCount).Hidden = True) RowCount = RowCount + 1 Loop For LoopCount = 0 To (Count - 1) .Cells(RowCount, "I"). _ Offset(0, (2 * Ref) + MyOffset) = _ MyArray(LoopCount, SO) .Cells(RowCount, "Q"). _ Offset(0, (2 * Ref) + MyOffset) = _ MyArray(LoopCount, OP) If MyOffset = 0 Then .Cells(RowCount, "H").Value = Model MyOffset = 1 Else RowCount = RowCount + 1 Do While (Not IsEmpty(.Cells(RowCount, "I")) And _ (.Cells(RowCount, "H") < Model)) Or _ (.Cells(RowCount, "I") = "ASC") Or _ (.Rows(RowCount).Hidden = True) RowCount = RowCount + 1 Loop MyOffset = 0 End If Next LoopCount End With --- "Bob Bridges" wrote: I'm looking at your code and I don't think it does what you think it does. I understood you to say that on each row where "ASC" is in col I it skips filling in data for cols I thru N. What this code actually seems to do is check I and if it isn't "ASC" it CLEARS the contents of H thru X. I haven't examined the same sections for Sheets("200") and Sheets("300") but they seem to be similar. Seems to me this needs to be straightened out before we can continue. --- "Cam" wrote: I have the following existing code which look at a column "I" and if the word "ASC" text is present in the cell, then skip filling the data in column I thru N for the row. What I would like to change the code so that it will fill the data in the cell from column I thru N that does not have "ASC" on the cell. Old code sample: A I J K L M N Line# 1300 1300 1500 1500 1700 1700 001 ASC 002 x x x x x x 003 x x x x x x skip row 2 with line# 001 cause ASC is in column I2 so skipping cell J2 thru N2). Where x is data that the macro fill in. New code wanted: A I J K L M N Line# 1300 1300 1500 1500 1700 1700 001 ASC x ASC x x x 002 x ASC x x x x 003 x x x x x x it does not skip row 2 with line# 001, but rather fill in the missing cell (J2, L2, M2 and N2) without "ASC" in the cell. Here is my existing code: Private Sub Macro() ' Dim R1300M100(10000, 3) Dim R1300M200(10000, 3) Dim R1300M300(10000, 3) Dim R1500M100(10000, 3) Dim R1500M200(10000, 3) Dim R1500M300(10000, 3) Dim R1700M100(10000, 3) Dim R1700M200(10000, 3) Dim R1700M300(10000, 3) Dim R1100M100(10000, 3) Dim R1100M200(10000, 3) Dim R1100M300(10000, 3) With Sheets("100") LastRowSh1 = _ .Cells(Rows.Count, "I").End(xlUp).Row If LastRowSh1 < 1 Then Set ColIRange = .Range(.Cells(2, "I"), _ .Cells(LastRowSh1, "I")) For Each Cell In ColIRange If (Cell < "ASC") And _ (.Rows(Cell.Row).Hidden = False) Then .Range("H" & Cell.Row & ":X" & Cell.Row).ClearContents End If Next Cell End If End With With Sheets("200") LastRowSh2 = _ .Cells(Rows.Count, "I").End(xlUp).Row If LastRowSh2 < 1 Then Set ColIRange = .Range(.Cells(2, "I"), _ .Cells(LastRowSh2, "I")) For Each Cell In ColIRange If (Cell < "ASC") And _ (.Rows(Cell.Row).Hidden = False) Then .Range("H" & Cell.Row & ":X" & Cell.Row).ClearContents End If Next Cell End If End With With Sheets("300") LastRowSh3 = _ .Cells(Rows.Count, "I").End(xlUp).Row If LastRowSh3 < 1 Then Set ColIRange = .Range(.Cells(2, "I"), _ .Cells(LastRowSh3, "I")) For Each Cell In ColIRange If (Cell < "ASC") And _ (.Rows(Cell.Row).Hidden = False) Then .Range("H" & Cell.Row & ":X" & Cell.Row).ClearContents End If Next Cell End If End With LastRowSh4 = Sheets("Data"). _ Cells(Rows.Count, "A").End(xlUp).Row More code to insert data, etc..... |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Really need help with existing code. | Excel Discussion (Misc queries) | |||
Modify existing code to dynamic code | Excel Programming | |||
help with altering existing code | Excel Programming | |||
Help with existing code | Excel Discussion (Misc queries) | |||
Insert into existing code | Excel Programming |