Link to home
Start Free TrialLog in
Avatar of kev-griggs
kev-griggsFlag for Afghanistan

asked on

Offset Variables in Formulas Excel Macro VBA 2010

Hi,

I have an excel spreadsheet (see attached) where I need to loop through each row looking at the value in 'G' (starting at G4).

What I would like it to do is monitor the times taken in the 6 different sections, so based on the entry in there (i.e. 'G1') look to the values on Sheet 2 (in column A), match it, and put the date in column X minus the relevant amount of days in each column.

For Example, because cell G4 = 'G1', the value for L4 (Panels) will be X4 -1-1-7-5-6-10 (all the sections on sheet 2 corresponding to G1, and then M4 (In Rig) will be X4 -1-1-7-5-6 but not the 10 as the panels takes 10 days according to the test values in sheet 2.

Lastly, I only want this to be applied if the values in L,M,N,O,V and W are blank (as we will replace with percentages when they are in the relevant sections and X's when they are completed and moved on).


Would be grateful for any pointers, I have attempted an offset and a loop method but this hasn't been successful.

Regards,
Ben
Test.xlsm
Avatar of byundt
byundt
Flag of United States of America image

Here is a macro that will perform your update. I wasn't sure where the "X4" was coming from, so I guessed that the "X" was standard and that the 4 referred to the row that the cell in column G was on.
Sub Process2()
Dim cel As Range, rg As Range, targ As Range, rgLookup As Range, rw As Range
Dim strPanels As String, strRig As String
Dim i As Long
Application.ScreenUpdating = False
With Worksheets("StandardTimes")
    Set rgLookup = .Range("A1:G12")     'A better practice would be to create a named range for the lookup table
End With
With Worksheets("GalleyStatus")
    Set rg = .Range("G4")   'First cell being processed
    Set rg = .Range(rg, .Cells(.Rows.Count, rg.Column).End(xlUp))
    On Error Resume Next
    For Each cel In rg.Cells
        Set rw = cel.EntireRow
        If Application.CountA(rw.Range("L1:O1"), rw.Range("V1:W1")) = 0 Then
            Set targ = Nothing
            Set targ = rgLookup.Find(cel.Value, LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=False)
            If Not targ Is Nothing Then
                strRig = "X" & cel.Row      'Or perhaps ="X4"
                For i = 7 To 3 Step -1
                    strRig = strRig & "-" & targ.Cells(1, i).Value
                Next
                strPanels = strRig & "-" & targ.Cells(1, 2).Value
                rw.Range("L1").Value = strPanels
                rw.Range("M1").Value = strRig
            End If
        End If
    Next
End With
End Sub

Open in new window

Brad
Avatar of kev-griggs

ASKER

Hi Brad,

One quick thing, how do I expand the formula for the other sections (Paint, Finals etc)

I've given it a go but not having much luck here!

Ben
Ben,
I think I completely missed your aim. You are looking for a formula , not a macro--right? And what I saw as a concatenation, should really be a series of subtractions.

If so, then consider:
=$X4-SUM(INDEX(tbLookup,MATCH($G4,INDEX(tbLookup,,1),0),MATCH(L$2,INDEX(tbLookup,1,),0)):INDEX(tbLookup,MATCH($G4,INDEX(tbLookup,,1),0),COLUMNS(tbLookup)))

This formula is using a named range tbLookup that refers to worksheet StandardTimes cells A1:G12.

I used a Custom format of d\.m\.yy to get your dates to look like 13.3.13 for 13 March 2013.

When I copied the formula down, row 24 returned #N/A. Upon investigation, I found that cell G24 contained a space after "G2 ". After removing that space, the formulas worked.

Brad
Test-1Q28079674.xlsm
Brad,

I really don't mind, I couldn't figure out a formula that worked, but this appears to, the only problem is this will be constantly added to, so new lines will be added and pasting the formula down might be tricky for the end user (maybe the macro was along the right track, just not for all sections?).

Ben
ASKER CERTIFIED SOLUTION
Avatar of byundt
byundt
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Fantastic, Thank you very much Brad
Really helpful and tweaked it a few times without any fuss.
Hi Brad,

A really awkward moving target, they've now requested the dates going back are Working Days (Weekdays Mon-Fri) only. Would this be an easy change to implement?

Ben
did it! dont worry. thank you

"=WORKDAY(RC24,-SUM(INDEX(tbLookup,MATCH(RC7,INDEX(tbLookup,,1),0),MATCH(R2C,INDEX(tbLookup,1,),0)):" & _
                        "INDEX(tbLookup,MATCH(RC7,INDEX(tbLookup,,1),0),COLUMNS(tbLookup))))"