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

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.


--
Regards,

OssieMac


"Steved" wrote:

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

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
--
Steve

"Steved" wrote in message
...
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

Hello from Steved

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

Thankyou.


"OssieMac" wrote:

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.


--
Regards,

OssieMac


"Steved" wrote:

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

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)"


--
Steve

"Steved" wrote in message
...
Hello from Steved

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

Thankyou.


"OssieMac" wrote:

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.


--
Regards,

OssieMac


"Steved" wrote:

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

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.

--
Regards,

OssieMac


"Steved" wrote:

Hello from Steved

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

Thankyou.


"OssieMac" wrote:

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.


--
Regards,

OssieMac


"Steved" wrote:

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

Thankyou

"OssieMac" wrote:

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.

--
Regards,

OssieMac


"Steved" wrote:

Hello from Steved

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

Thankyou.


"OssieMac" wrote:

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.


--
Regards,

OssieMac


"Steved" wrote:

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

Thankyou

"AltaEgo" wrote:

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
--
Steve

"Steved" wrote in message
...
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