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.