compile error

[Pete]

Hi getting a compile error Argument not optional

ws.Cells(startrow + 1, curCol).Formula = Replace("=(('Pricing Supply'!^1" & startrow + 1 & "+'Shipping UFC'!^1" & startrow + 1 & ")/(1-'Shipping BOG'!^1" & startrow + 1 & "))* 'Modelling (Vol)'!^1" & startrow + 1 & "^1", Mid(Cells(1, curCol).Address, 2, InStr(2, Cells(1, curCol).Address, "$") - 2))

[Mavyak]

It looks like you told the Replace function what to search and what to find but not what to replace what it found with.

Variable= ws.Cells(startrow + 1, curCol).Formula
What to search = Replace("=(('Pricing Supply'!^1" & startrow + 1 & "+'Shipping UFC'!^1" & startrow + 1 & ")/(1-'Shipping BOG'!^1" & startrow + 1 & "))* 'Modelling (Vol)'!^1" & startrow + 1 & "^1"
What to find = Mid(Cells(1, curCol).Address, 2, InStr(2, Cells(1, curCol).Address, "$") - 2))

There should be a comma between the last two closing parentheses followed by what to replace the found string with. It will probably auto highlight the argument "Replace As String" for you when you type in the comma.

[Pete]

apologies for asking but i am lost.....

[Mavyak]

The Replace function takes (at minimum) three arguments:
"Expression as String" (what to search)
"Find as String" (what to find)
"Replace as String" (what to replace it with)

You provided the function what to search and what to find but not what to replace it with.

For detailed information on the Replace function see this link:

http://msdn.microsoft.com/en-us/library/aa241892(VS.60).aspx (http://msdn.microsoft.com/en-us/library/aa241892%28VS.60%29.aspx)

[mdmackillop]

I would create a variable for each argument identified by Mayvak in his last post, and insert these in the function. You can easily check then for valid values to pin down your problem.