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?

 

[Cam]

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]

I take it you didn't write the original code, either. It can be dangerous to
guide someone to modifying a program they can't understand for themselves;
"can't understand" leads to "misunderstand", and then bad things happen. And
if I just post some code to replace yours, either it goes wrong because I
failed to understand the original code myself, or at best it perpetuates the
problem: You're responsible for maintaining code that you're nevertheless
unable to maintain because you don't understand it.

Others here may disagree and be perfectly willing to post some more detailed
code, but in my opinion it's time to teach you how to do the routine
yourself, from scratch -- or at least after stripping it down to the part you
understand, building it up again from there using your own brain with my
tutoring. Then you'll know what you did, and why, and can see for yourself
what you need to upgrade when you want to.

If you're willing to go this route, I think it's a good idea; you'll be able
to write your own programs afterward, which is usually a good thing. (Well,
I think it's ALWAYS a good thing, but then I'm a programmer.) Contact me at
(e-mail address removed) and we'll start from the beginning. Otherwise, see
whether someone else will give you the help you want. You may need to repost
the question so others will see it and respond, adding a note like "Bob
Bridges, stay out of it this time!" .