Compile error, Syntax error

[Steved]

Hello from Steved


I should know but I'm not thinking to clearly.

I am getting a Compile error, Syntax error for the below why I thankyou.

Sub Removezero()
ActiveCell.FormulaR2C3 = "=MID(B2,1+(LEFT(B2)="0"),99)"
End Sub

 

[OssieMac]

Not sure what you are trying to do. Can you post a sample of the source data
and a sample of the result that the formula should return.

 

[AltaEgo]

I suspect you are trying to enter a formula in C2 that works on at cell B2
but I cannot work out exactly what you are trying to achieve with
1+(LEFT(B2)="0").

The alternative way to work out your problem is to physically write the
formula in the desired cell then copy it to VBA:

ActiveCell.Formula = "yourPastedFormula"

or Range("C3").Formula = "yourPastedFormula"

HTH

 

[Steved]

Hello from Steved

In Col B:B I'm asking to be removed the leading "0" ie 07075601 to be 7075601

Thankyou.

 

[AltaEgo]

It seems you were after:

"=RIGHT(B2,LEN(B2)-1)"


This is better (no need to activate cells)

Sub TrimBCol1stChar()

Range("C2:C25").Formula = "=RIGHT(RC[-1],LEN(RC[-1])-1)"

'substitute C2:C25 with our range
'requiring the formula.

End Sub




NOTES:
1) the above will trim the length irrespective whether the first character
is 0.
2) the [-1] in the formula is telling EXCEL to work out the reference for
the same row, one column left In other words, if you wanted to put it in D
instead of C to still work for column B values:

Range("d2:d25").Formula = "=RIGHT(RC[-2],LEN(RC[-2])-1)"

 

[OssieMac]

I assume that the source is text and NOT numeric with the leading zero
present due to the number format being "00000000".

Either of the following:-

'This code first converts to numeric value then the TRIM function will
return the result as text.
ActiveCell.Formula = "=TRIM(VALUE(B1))"

'This code simply returns all after the first character. Result is text.
ActiveCell.Formula = "=MID(B1,2,255)"

NOTE: You cannot use these formulas in the same cell as the source. If you
want to replace the source then you need to do it differently..

To replace the source values without the leading zeros, then assuming the
the values in column B are text, you can first format the column to general
or number (no decimal places) and then insert the numeral 1 in any blank
cell. Copy the cell with 1 and then select all the data in column B and Paste
Special -> Multiply.

 

[Steved]

Thankyou

I assume that the source is text and NOT numeric with the leading zero
present due to the number format being "00000000".

Either of the following:-

'This code first converts to numeric value then the TRIM function will
return the result as text.
ActiveCell.Formula = "=TRIM(VALUE(B1))"

'This code simply returns all after the first character. Result is text.
ActiveCell.Formula = "=MID(B1,2,255)"

NOTE: You cannot use these formulas in the same cell as the source. If you
want to replace the source then you need to do it differently..

To replace the source values without the leading zeros, then assuming the
the values in column B are text, you can first format the column to general
or number (no decimal places) and then insert the numeral 1 in any blank
cell. Copy the cell with 1 and then select all the data in column B and Paste
Special -> Multiply.

 

[Steved]

Thankyou

I suspect you are trying to enter a formula in C2 that works on at cell B2
but I cannot work out exactly what you are trying to achieve with
1+(LEFT(B2)="0").

The alternative way to work out your problem is to physically write the
formula in the desired cell then copy it to VBA:

ActiveCell.Formula = "yourPastedFormula"

or Range("C3").Formula = "yourPastedFormula"

HTH