VBA Alter Formula Depending on ActiveSheet Criteria

Ottsel

Board Regular
Joined
Jun 4, 2022
Messages
106
Office Version
  1. 365
Platform
  1. Windows
I have a sheet where I need to extract a small array of data and using reference formulas saves a lot of time, but I feel there is a way to structure it, so it can a bit more automated.
Row 1 is my header, and column A contains data that'll help fill out the other cells

Columns B:F will contain the formula and depending on what's if column A contains any values it'll need to be entered.

The beginning of the reference is always:
='G:\JC\
After which the content in 'G1' \ 'A1' \

Then I encounter one that usually always stops me from being able to complete this, which is the first half is always the same followed by the file type, but the number will change, which depends on what is in column A

1660759304817.png

CMT Column A
# Column B
REF Column C
ID Column D
LOT COLUM E
NAME Column F

the endings of the formula change depending on the column they're in, but will be structured the same after all the references have been met.
After being completed will look like this:

B2
Excel Formula:
='G:\JC\KBH\CMT\[Lot84.xls]Plan'!$AN$4
C2
Excel Formula:
='G:\JC\KBH\CMT\[Lot84.xls]LCV'!$M$3
D2
Excel Formula:
='G:\JC\KBH\CMT\[Lot84.xls]Plan'!$B$11
E2
Excel Formula:
='G:\JC\KBH\CMT\[Lot84.xls]Fm'!$I$8
F2
Excel Formula:
='G:\JC\KBH\CMT\[Lot84.xls]Jcm'!$N$5

Column A could contain multiple inputs, but normally ranges anywhere from 1-10 entries.

Any help, ideas or code examples would be highly appreciated.
 

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.

Ottsel

Board Regular
Joined
Jun 4, 2022
Messages
106
Office Version
  1. 365
Platform
  1. Windows
Well, I managed to get it to work, but had to use an IF string, which is.. err... I know it could be better with a loop, but i'm still not the best with those. If anyone would like to convert this to a loop I'd appreciate it, so I could take notes from it.

VBA Code:
Option Explicit

Sub ImportData()

    Dim FormulaStart As String
    Dim FormulaHook As String
    Dim PC As String
    Dim Address As String
    Dim Plan As String
    Dim Elev As String
    Dim Erifco As String
   
    FormulaStart = "='G:\JOBCARDS\" & ActiveSheet.Range("G1").Value & "\" & _
        ActiveSheet.Range("A1").Value & "\[Lot"
    FormulaHook = ".xls]"
    PC = "Plan'!$AN$4"
    Address = "LCV'!$M$3"
    Plan = "Plan'!$B$11"
    Elev = "Foreman'!$I$8"
    Erifco = "JobCard'!$N$5"
    'Prevent from Forumlas filling all the way down
    Application.AutoCorrect.AutoFillFormulasInLists = False

    'Row 2
    If ActiveSheet.Range("A2") > "" Then
        Range("B2").Value = FormulaStart & Range("A2").Value & FormulaHook & PC
        Range("C2").Value = FormulaStart & Range("A2").Value & FormulaHook & Address
        Range("D2").Value = FormulaStart & Range("A2").Value & FormulaHook & Plan
        Range("E2").Value = FormulaStart & Range("A2").Value & FormulaHook & Elev
        Range("F2").Value = FormulaStart & Range("A2").Value & FormulaHook & Erifco
    End If
   
    'Row 3
    If ActiveSheet.Range("A3") > "" Then
        Range("B3").Value = FormulaStart & Range("A3").Value & FormulaHook & PC
        Range("C3").Value = FormulaStart & Range("A3").Value & FormulaHook & Address
        Range("D3").Value = FormulaStart & Range("A3").Value & FormulaHook & Plan
        Range("E3").Value = FormulaStart & Range("A3").Value & FormulaHook & Elev
        Range("F3").Value = FormulaStart & Range("A3").Value & FormulaHook & Erifco
    End If

    'Row 4
    If ActiveSheet.Range("A4") > "" Then
        Range("B4").Value = FormulaStart & Range("A4").Value & FormulaHook & PC
        Range("C4").Value = FormulaStart & Range("A4").Value & FormulaHook & Address
        Range("D4").Value = FormulaStart & Range("A4").Value & FormulaHook & Plan
        Range("E4").Value = FormulaStart & Range("A4").Value & FormulaHook & Elev
        Range("F4").Value = FormulaStart & Range("A4").Value & FormulaHook & Erifco
    End If

    'Row 5
    If ActiveSheet.Range("A5") > "" Then
        Range("B5").Value = FormulaStart & Range("A5").Value & FormulaHook & PC
        Range("C5").Value = FormulaStart & Range("A5").Value & FormulaHook & Address
        Range("D5").Value = FormulaStart & Range("A5").Value & FormulaHook & Plan
        Range("E5").Value = FormulaStart & Range("A5").Value & FormulaHook & Elev
        Range("F5").Value = FormulaStart & Range("A5").Value & FormulaHook & Erifco
    End If
   
    'Row 6
    If ActiveSheet.Range("A6") > "" Then
        Range("B6").Value = FormulaStart & Range("A6").Value & FormulaHook & PC
        Range("C6").Value = FormulaStart & Range("A6").Value & FormulaHook & Address
        Range("D6").Value = FormulaStart & Range("A6").Value & FormulaHook & Plan
        Range("E6").Value = FormulaStart & Range("A6").Value & FormulaHook & Elev
        Range("F6").Value = FormulaStart & Range("A6").Value & FormulaHook & Erifco
    End If
   
    'Row 7
    If ActiveSheet.Range("A7") > "" Then
        Range("B7").Value = FormulaStart & Range("A7").Value & FormulaHook & PC
        Range("C7").Value = FormulaStart & Range("A7").Value & FormulaHook & Address
        Range("D7").Value = FormulaStart & Range("A7").Value & FormulaHook & Plan
        Range("E7").Value = FormulaStart & Range("A7").Value & FormulaHook & Elev
        Range("F7").Value = FormulaStart & Range("A7").Value & FormulaHook & Erifco
    End If
   
    'Row 8
    If ActiveSheet.Range("A8") > "" Then
        Range("B8").Value = FormulaStart & Range("A8").Value & FormulaHook & PC
        Range("C8").Value = FormulaStart & Range("A8").Value & FormulaHook & Address
        Range("D8").Value = FormulaStart & Range("A8").Value & FormulaHook & Plan
        Range("E8").Value = FormulaStart & Range("A8").Value & FormulaHook & Elev
        Range("F8").Value = FormulaStart & Range("A8").Value & FormulaHook & Erifco
    End If
   
    'Row 9
    If ActiveSheet.Range("A9") > "" Then
        Range("B9").Value = FormulaStart & Range("A9").Value & FormulaHook & PC
        Range("C9").Value = FormulaStart & Range("A9").Value & FormulaHook & Address
        Range("D9").Value = FormulaStart & Range("A9").Value & FormulaHook & Plan
        Range("E9").Value = FormulaStart & Range("A9").Value & FormulaHook & Elev
        Range("F9").Value = FormulaStart & Range("A9").Value & FormulaHook & Erifco
    End If
   
    'Row 10
    If ActiveSheet.Range("A10") > "" Then
        Range("B10").Value = FormulaStart & Range("A10").Value & FormulaHook & PC
        Range("C10").Value = FormulaStart & Range("A10").Value & FormulaHook & Address
        Range("D10").Value = FormulaStart & Range("A10").Value & FormulaHook & Plan
        Range("E10").Value = FormulaStart & Range("A10").Value & FormulaHook & Elev
        Range("F10").Value = FormulaStart & Range("A10").Value & FormulaHook & Erifco
    End If
   
    'Formula be-gone
    Range("A2:G10").Copy
    Range("A2:G10").PasteSpecial Paste:=xlPasteValues
    Application.CutCopyMode = False
    Range("A1").Activate
   
    're-enable this feature
    Application.AutoCorrect.AutoFillFormulasInLists = True
   
End Sub
 

Forum statistics

Threads
1,181,322
Messages
5,929,300
Members
436,660
Latest member
Mouseinalabyrinth

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top