How to loop through rows and changing the reference worksheet in each cell-CodePudding

I'm new to VBA and was just trying to figure this out.

I have a the following range CR7, CR9:CR24, CR28:CR39, CR45:CR50, CR52

Currently, each of these cells reference a value from another worksheet.

CR7='Trend'!CO7
CR9='Trend'!CO9
CR10='Trend'!CO10

This will follow the entire range.

What I was wanting to do was change the reference to 3 columns to the right.

CR7='Trend'!CO7 will then be CR7='Trend'!CR7

What I was trying to do was remove the last 3 characters after the "!" and then replace it with "RC[-3]" but I don't know what I'm doing at all. I just looked up different codes on this site and tried to piece it together, knowing that I would probably run into some obstacles because I'm new at this.

Here's the code I was trying to implement but running into an error.

For Each C In Range("CR7,CR9:CR24,CR28:CR39,CR45:CR50,CR52")
   C.Value = Left(C.Value, InStr(C.Value, "!") - 1)
   C.Value = C.Value & "RC[-3]"
Next

Any suggestions on how to get this corrected or is there a better way in doing this? Thanks in advance for looking into this!!

CodePudding user response：

Solution
As stated in the comments, since you are looking for a specific header, the solution might be faster as follows:

Assuming the headers are in Sheet 1 as follows

Jan-22 (IE:actual data is 01-01-2022)	Feb-22	Mar-22
10	30	60
20	40	70

Where A1 = Jan-22, B2= Feb-22, etc.

Just use a combination of index and match, seems like you need to bring the row at the same level, so it will be as follows:

Set a referece in your formula to where you will have the criteria to look: In this example will be A1

=INDEX(Sheet1!$A:$C,ROW(),MATCH($A$1,Sheet1!$1:$1,0))

I'll explain:

Index will bring the column where you want the data, in this case will be the data under the month (Col A), seems like the row should be at the same level, so we will say the row will be equal to where the formula row is being casted, that's why the row(), finally, it will be in where the column matches the criteria that you want where the header is found, if you need an offset even after finding the column name, just sum it up at that part

MATCH($A$1,Sheet1!$1:$1,0)   3

Demo: