VBA to Change Cell References in Columns Based on String in Another Worksheet

%3CLINGO-SUB%20id%3D%22lingo-sub-3319404%22%20slang%3D%22en-US%22%3EVBA%20to%20Change%20Cell%20References%20in%20Columns%20Based%20on%20String%20in%20Another%20Worksheet%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3319404%22%20slang%3D%22en-US%22%3E%3CP%3EHello%20all%2C%20first%20time%20posting%20here%20and%20I%E2%80%99m%20a%20VBA%20novice%20so%20hopefully%20I%20can%20explain%20what%20I%E2%80%99m%20trying%20to%20do%20clearly%E2%80%A6.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%E2%80%99m%20trying%20to%20change%20a%20cell%20reference%20in%20an%20entire%20column%20of%20a%20specific%20worksheet%20based%20on%20the%20value%20of%20a%20cell%20in%20another%20worksheet%20%E2%80%9CBILL%20OF%20MATERIAL%E2%80%9D.%20Here%20is%20the%20breakdown%20of%20what%20I%E2%80%99m%20trying%20to%20do%3A%3C%2FP%3E%3CP%3E%E2%80%9CBILL%20OF%20MATERIAL%E2%80%9D%20worksheet%20has%20a%20cell%20that%20has%20the%20address%20(example%3A%20%E2%80%9CU5%E2%80%9D)%20of%20the%20cell%20that%20has%20the%20name%20of%20the%20sheet%20(example%20%E2%80%9CC5.10%E2%80%9D)%20I%20want%20to%20make%20changes%20to.%20When%20I%20run%20a%20macro%2C%20I%20want%20the%20sheet%20with%20that%20name%20to%20have%20a%20whole%20column%20selected%20(always%20column%20%E2%80%9CE%E2%80%9D)%20and%20change%20the%20following%20reference%20in%20each%20cell%2C%20essentially%20shifting%20the%20reference%20one%20cell%20to%20the%20left%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EExisting%20string%3A%3C%2FP%3E%3CP%3E%3DIF('BILL%20OF%20MATERIAL'!%24U12%26gt%3B0%2C'BILL%20OF%20MATERIAL'!U12%2C%22%22)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EChange%20to%3A%3C%2FP%3E%3CP%3E%3DIF('BILL%20OF%20MATERIAL'!%24U12%26gt%3B0%2C'BILL%20OF%20MATERIAL'!T12%2C%22%22)%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3319404%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
Visitor

Hello all, first time posting here and I’m a VBA novice so hopefully I can explain what I’m trying to do clearly….

 

I’m trying to change a cell reference in an entire column of a specific worksheet based on the value of a cell in another worksheet “BILL OF MATERIAL”. Here is the breakdown of what I’m trying to do:

“BILL OF MATERIAL” worksheet has a cell that has the address (example: “U5”) of the cell that has the name of the sheet (example “C5.10”) I want to make changes to. When I run a macro, I want the sheet with that name to have a whole column selected (always column “E”) and change the following reference in each cell, essentially shifting the reference one cell to the left:

 

Existing string:

=IF('BILL OF MATERIAL'!$U12>0,'BILL OF MATERIAL'!U12,"")

 

Change to:

=IF('BILL OF MATERIAL'!$U12>0,'BILL OF MATERIAL'!T12,"")

0 Replies