I'm using Excel 2003 and hope someone here can help me.
With the help of other forum users I managed to get a macro which distributes values across a number of rows (code below).
In my Excel table shown below the yellow rows have been inserted with a macro.
The macro basically looks whether there is a value under SiteBalance (column P) and distributes that value across the number
of rigs working for a site.
So for the example in yellow, the SiteBalance is $64,517.
The site in cell N21 is shown as 'NMM'.
The macro now looks at cell F8 and knows there are 8 rigs on that site so it's $64,517/8.
Row 13 to 20 are then inserted.
What I need help with is finding a way to insert the rig number as well in cell M13 to M20. Currently it only has the site
description 'NMM' but I'm trying to get it to show all the rig numbers from the grey underlayed section on the top.
So for example if the site is NMM, the value s devided by 8 and in cells M13 to M20 the number of the rig is entered from
cell G2 to T2.
With a formula I would do it like this:
I'm looking for a way to include this in my code however.
I'm really stuck so any help is greatly appreciated!
My Excel Table
Dim aRows, AMatchCols
Dim LR As Long, r As Long, RwsReqd As Long
Dim i As Long, j As Long, k As Long, x As Long, z As Long
Dim Amt As Single
Dim Site As String, CSS1 As String, CSS2 As String
Const FR As Long = 11 '<-- First Row of actual data
Const NumSites As Long = 4 '<--No. of possible sites
AMatchCols = Array("J", "K", "N") '<--Cols that must match (CSS)
Application.ScreenUpdating = False
x = UBound(AMatchCols)
aRows = Range("E1:F" & NumSites).Value
LR = Range("P" & Rows.Count).End(xlUp).Row
Range("E" & FR & ":P" & LR).Sort Key1:=Range("J" & FR),
Key2:=Range("K" & FR), Order2:=xlAscending, Key3:=Range("N" & FR), _
Order3:=xlAscending, Header:=xlNo, OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, _
r = LR
If Cells(r, "P") <> 0 Then
Site = Cells(r, AMatchCols(2)).Value
i = 0
i = i + 1
Loop Until aRows(i, 1) = Site Or i = NumSites
If aRows(i, 1) = Site Then
RwsReqd = aRows(i, 2)
MsgBox "Site not found in table"
Amt = Cells(r, "P").Value / RwsReqd
CSS1 = ""
For j = 0 To x
CSS1 = CSS1 & "|" & Cells(r, AMatchCols(j)).Value
For k = 1 To RwsReqd
' r = r - 1
If r >= FR - 1 Then
z = r
CSS2 = ""
For j = 0 To x
CSS2 = CSS2 & "|" & Cells(r - 1, AMatchCols(j)).Value
z = FR
If CSS2 = CSS1 Then
Cells(r - 1, "Q").Value = Amt
Cells(z, "E").Resize(, 11).Value = _
Cells(z + 1, "E").Resize(, 11).Value
Cells(z, "Q") = Amt
r = r + 1
r = r - 1
r = r - 1
Loop While r >= FR
Application.ScreenUpdating = True