Really Help with existing VBA code...

[Cam]

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.....

 

[Bob Bridges]

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]

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]

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?