Thread: Find method to read reference or change reference to value.

I have two sets of code

For Each d In Range("Prov1")
d.Select
ActiveCell.Offset(2, 0).Select
ActiveCell.FormulaR1C1 = "=Cansim!R[-1]C"
ActiveCell.Offset(14, 0).Select
ActiveCell.FormulaR1C1 = "=Cansim!R[-14]C"
ActiveCell.Offset(14, 0).Select
ActiveCell.FormulaR1C1 = "=Cansim!R[-27]C"
ActiveCell.Offset(14, 0).Select
ActiveCell.FormulaR1C1 = "=Cansim!R[-40]C"
ActiveCell.Offset(14, 0).Select
ActiveCell.FormulaR1C1 = "=Cansim!R[-53]C"
Next d

As you can see, this sets my desired active cells to be the same as the values from sheet “Cansim”. This works great!

This code however, does not:

For Each c In Range("WPGCansim")
c.Select
Application.Worksheets("Insert").Select
Set r = Worksheets("Insert").Range("CansimSourceNo").Find(c)
If Not r Is Nothing Then r.Select

This code is trying to find the proper identifier but it cannot. I’ve figured out it is because the identifier to be found is a reference to a value, not the value itself. Is there a way to have the .Find method see the value of the cells instead of the reference?

Sorry if this makes no sense but me brain is all tricksee because of dealing with would-be car thieves last night and I’m downright tired. If needed I can upload the workbook.

Thanks

The first code snippet retrieves all of the identifiers and places them properly into the Insert sheet (which is the data that comes from our source). I've done this because our data sheets use identifiers to fill in the data values from the source and the source does not supply the identifiers on their data tables.
The second code is from a sub that finds the identifiers from the Insert sheet based on the identifiers from our data sheet so that it can copy the data from the insert and paste it into our data sheets.

The problem is, the second snippet of code does not find the values in each cell in the Insert sheet. The first identifier it should find looks like this on the sheet: v735047. But, it is actually a reference that reads: =Cansim!L3. I think it is because it is a reference and not and actual value that the second code won't read it. Am I making sense or do you want to see the workbook?

I don't quite understand your explanation so I'm wondering why you are putting a formula into the cells instead of the actual value:

    For Each d In Range("Prov1")
        d.Offset(2, 0).Value = Sheets("Cansim").Cells(d.Row - 1).Value
etc

??

I've uploaded the workbook so you can get a visual of what I am doing.

Now that you mention it proton, maybe that is what I will do. I'll just copy each identifier over instead. I'll give it a go and let you know how it.....goes?!

I don't know and I don't think I am apt to understand today. I don't really want to copy each and every cell and then paste it into the Insert sheet because it appears like it will take far too much coding.

I tried your code proton and it copies blank values based on the same row criteria as: R[-1]C

You need to search the values not the formulas:

Set r = Worksheets("Insert").Range("CansimSourceNo").Find(what:=c, LookIn:=xlValues)

That did it romperstomper. Thank you so much.