Avatar of kev-griggs
kev-griggs
Flag 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
Microsoft ExcelVB ScriptSpreadsheets

Avatar of undefined
Last Comment
kev-griggs

8/22/2022 - Mon
byundt

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
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
byundt

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
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
kev-griggs

ASKER
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
byundt

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
kev-griggs

ASKER
Fantastic, Thank you very much Brad
kev-griggs

ASKER
Really helpful and tweaked it a few times without any fuss.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
kev-griggs

ASKER
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
kev-griggs

ASKER
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))))"