Find replace using cells own row number

[nigelog]

As a quick fix in a larger macro I am trying to replace the carried over "=#REF!*F and its its row number" with "D and its row number * F and and its row number"
I tried this but it gives a static reference to whatever cell is selected at the time

Please Login or Register  to view this content.

Any help appreciated

[kev_]

If you are simply replacing the F with a D
Try this one-liner

Please Login or Register  to view this content.

I was not sure what the * meant - is it used as a wildcard or is it a character in the cell?
- if that causes solution not to give you what you want, then I need to know more about the original values

[nigelog]

Hi Kev

a formula paste gives a reference error because the column carried to another sheet moves position

result

Please Login or Register  to view this content.

when the preferred result would be

Please Login or Register  to view this content.

[kev_]

Do all the cells in the column have the problem or only some of them?

[nigelog]

Some have a formula which is correct these cells do not

[kev_]

I would like to see the actual formulas etc
Can you post sample workbook

Click GoAdvanced \ look below and click on ManageAttachments \ follow screen instructions

[mjr veverka]

Please Login or Register  to view this content.

"Selection.Row" - works every time I try, and this "Selection.Cells(1).Row" also, while "Replace" does not work from the vba level.

[nigelog]

Storm08's workbook I was assisting with - need to carry formulas to sheet2

right click to see control menu

client update refers to Module7.CopyRows

[mjr veverka]

"ReplaceText" is reserved word in vba ... Read/write Boolean.

Edit:

Maybe something like that ?

Please Login or Register  to view this content.

[kev_]

You will need to refer to the correct worksheet.

Please Login or Register  to view this content.

[nigelog]

Perfect much appreciated Kev

[kev_]

You are welcome

Another way is to replace the formula itself

Please Login or Register  to view this content.

[nigelog]

The last issue is using the "sumbycolor" function to sum the cells

Ive tried replacing the formula in the cell

Please Login or Register  to view this content.

but no joy

[kev_]

I do not know what the function demands, but( based on your post)
this:

Please Login or Register  to view this content.

will give you this formula in the cell [if lastRow = 20] :

Please Login or Register  to view this content.

Edit1
The formula box is playing up (on my PC at least) - formula returned is:
=SumByColor(G10, G10:G18)

Edit2
The formula box is not playing up 30 minutes later

[nigelog]

Spot on Kev

works a treat

Thanks a lot