Inserting formulas
Posted by Cory on September 12, 2001 10:09 AM
I know there's a way to do this but I can't find it anywhere on this board. I've even checked the archives and used a search technique.
I have two columns of data. ColA = product number, ColB = product cost.
The products in these columns are separated into categories by what location in our warehouse they reside in. ie: shelf 1 on aisle B is called 2HOT1, and that shelf has parts x, y, z on it. 2HOT3 has parts a, b, c, d, and e on it, etc...
What I'm having to do is go to the end of each group of parts and enter a sum() formula to add up the total cost of the parts in each location (2HOT1, 2HOT3, etc...) My problem is that there's over 1000 locations and +20,000 rows of data to do this with.
How would I write a macro that would loop through this data, find the end of each location, and insert a formula adding up the values of the parts in that location. Mind you there's a different number of parts in each location? Here's kind of what it looks like before I enter the formulas in:
ColA________ColB
PartA.........10.00
PartB.........20.00
PartC.........15.00
Total 2HOT1...
PartZ.........10.00
PartY.........10.00
PartX.........15.00
PartW.........20.00
PartV.........25.00
Total 2HOT1...
TIA,
Cory
Posted by Mark O'Brien on September 12, 2001 12:42 PM
Cory,
Insert this code onto the worksheet that you want the totals to be on.
Public Sub sCreateFormula()
' Declare Variables
Dim rngFirstCell As Range
Dim intNumRows As Integer
Dim strFormula As String
Dim strTest As String
Dim i As Integer
' Initialise Variables
Set rngFirstCell = Me.Range("A1")
intNumRows = 9
strFormula = "=SUM(" & rngFirstCell.Offset(0, 1).Address
strTest = "Total"
' Start Loop
For i = 0 To intNumRows
With rngFirstCell
If 0 < InStr(1, .Offset(i, 0).Value, strTest, vbTextCompare) Then
.Offset(i, 1).Formula = strFormula & ":" _
& .Offset((i - 1), 1).Address & ")"
strFormula = "=SUM(" & .Offset((i + 1), 1).Address
End If
End With
Next
End Sub
The only things you should need to change are:
Set rngFirstCell = Me.Range("A1") --- To whatever cell the first part number is in.
intNumRows = 9 --- to however many rows you need to search through.
If you have any problems just repost.
Posted by Cory on September 13, 2001 10:54 AM
I didn't have any problems with the code except and error "inproper use of ME...", but fixed it by changing it to "activesheet". I just wanted to say thank you for teaching me something new. I haven't been able to figure out how to incorporate the InStr function and put it to good use until now. Again, thank you :-)
Cory
