MachaMacha

10-15-2007, 08:36 AM

I have attached a screenshot of the desired output.

The table in a16:g22 tells me how many of each type according to industry code (b16:g16) and amount (a17:a20). I want this table to be reflected in column M like the bolded values shown below. For example, there are 4 industry code 1, $10mm assets; therefore, in cells m2:m5 there are 4 $10mm.

There are 8 industry code 1, $25mm assets. Therefore m:5:m12 all say $25mm.

I would like VBA code that can fill in column M for me. What I have started is below. Thanks for any responses!

Sub FillSub()

Dim myRange2 As Range

Dim myArray2 As Variant

With ActiveSheet

Set myRange2 = Range(.Range("b6"), .Range("g6").End(xlDown))

End With

myArray2 = myRange2.Value

k = 1

For k = 1 To Range("g21").Value

Range("k1").Offset(k, 0).Value = k

Next k

Range("k1").Value = "Asset Number"

Range("k1").Offset(0, 1).Value = "Industry Code"

Range("k1").Offset(0, 2).Value = "Amount $"

Worksheets("Sheet1").Range("A1:M500").Columns.AutoFit

For Each c In Worksheets("Sheet1").Range("k2:k80").Cells

If Abs(c.Value) < Range("g22").Value Then c.Offset(0, 1).Value = Range("g16").Value

Next

For Each c In Worksheets("Sheet1").Range("k2:k74").Cells

If Abs(c.Value) < Range("f22").Value Then c.Offset(0, 1).Value = Range("f16").Value

Next

For Each c In Worksheets("Sheet1").Range("k2:k74").Cells

If Abs(c.Value) < Range("e22").Value Then c.Offset(0, 1).Value = Range("e16").Value

Next

For Each c In Worksheets("Sheet1").Range("k2:k74").Cells

If Abs(c.Value) < Range("d22").Value Then c.Offset(0, 1).Value = Range("d16").Value

Next

For Each c In Worksheets("Sheet1").Range("k2:k74").Cells

If Abs(c.Value) < Range("c22").Value Then c.Offset(0, 1).Value = Range("c16").Value

Next

For Each c In Worksheets("Sheet1").Range("k2:k74").Cells

If Abs(c.Value) < Range("b22").Value Then c.Offset(0, 1).Value = Range("b16").Value

Next

'Is there a way to condense all these For Each c lines into a smaller loop?

Dim myRange3 As Range

Dim myArray3 As Variant

With ActiveSheet

Set myRange3 = Range(.Range("k1"), .Range("m75").End(xlDown))

End With

Also, Is there a way to condense all these For Each c lines into a smaller loop? Any simpler solutions to what I have already written are much appreciated.

The table in a16:g22 tells me how many of each type according to industry code (b16:g16) and amount (a17:a20). I want this table to be reflected in column M like the bolded values shown below. For example, there are 4 industry code 1, $10mm assets; therefore, in cells m2:m5 there are 4 $10mm.

There are 8 industry code 1, $25mm assets. Therefore m:5:m12 all say $25mm.

I would like VBA code that can fill in column M for me. What I have started is below. Thanks for any responses!

Sub FillSub()

Dim myRange2 As Range

Dim myArray2 As Variant

With ActiveSheet

Set myRange2 = Range(.Range("b6"), .Range("g6").End(xlDown))

End With

myArray2 = myRange2.Value

k = 1

For k = 1 To Range("g21").Value

Range("k1").Offset(k, 0).Value = k

Next k

Range("k1").Value = "Asset Number"

Range("k1").Offset(0, 1).Value = "Industry Code"

Range("k1").Offset(0, 2).Value = "Amount $"

Worksheets("Sheet1").Range("A1:M500").Columns.AutoFit

For Each c In Worksheets("Sheet1").Range("k2:k80").Cells

If Abs(c.Value) < Range("g22").Value Then c.Offset(0, 1).Value = Range("g16").Value

Next

For Each c In Worksheets("Sheet1").Range("k2:k74").Cells

If Abs(c.Value) < Range("f22").Value Then c.Offset(0, 1).Value = Range("f16").Value

Next

For Each c In Worksheets("Sheet1").Range("k2:k74").Cells

If Abs(c.Value) < Range("e22").Value Then c.Offset(0, 1).Value = Range("e16").Value

Next

For Each c In Worksheets("Sheet1").Range("k2:k74").Cells

If Abs(c.Value) < Range("d22").Value Then c.Offset(0, 1).Value = Range("d16").Value

Next

For Each c In Worksheets("Sheet1").Range("k2:k74").Cells

If Abs(c.Value) < Range("c22").Value Then c.Offset(0, 1).Value = Range("c16").Value

Next

For Each c In Worksheets("Sheet1").Range("k2:k74").Cells

If Abs(c.Value) < Range("b22").Value Then c.Offset(0, 1).Value = Range("b16").Value

Next

'Is there a way to condense all these For Each c lines into a smaller loop?

Dim myRange3 As Range

Dim myArray3 As Variant

With ActiveSheet

Set myRange3 = Range(.Range("k1"), .Range("m75").End(xlDown))

End With

Also, Is there a way to condense all these For Each c lines into a smaller loop? Any simpler solutions to what I have already written are much appreciated.