using VBA remove "0" but leave "X" first digit only please.

S

Steved

Hello from Steved

Col B:B
Column format is General
0702301 to become 702301 but if it has an X702301 please ignore
Yes I'm only requiring the first digit of the cell to be changed ie delete
"0" if it has an "X" ignore it and find the next "0"

The bottom is similar as to what I'm trying to acheive please
Sub removezero()
Range("B2:B250").Formula = "=required Formula please"
End Sub

Thankyou
 
S

Steved

Hello from Steved

The below works as I require so what would I need to do please to have it
put in VBA and look in Col B:B to remove "0"

The below deletes the first digit if it is a "0" but leaves "X"

for example 0701301 it will become 701301 but if it has X701301 it will
ignore and find the next cell in Col B:B with "0"

=MID(B2,1+(LEFT(B2)="0"),99)

i THANKYOU
 
R

ryguy7272

There are MANY ways to do this. This may be one of the more intuitive ways;
hopefully easy to interpret the logic:

Sub DelZeros()
'Do Until ActiveCell = ""
For X = 1 To 1
Dim Rng As Range
Set Rng = Range("B2", Range("B56000").End(xlUp))
For Each cell In Rng
If cell.Value <> "" Then
ActiveCell.Offset(0, 1).Select
ActiveCell.FormulaR1C1 = "=MID(RC[-1],1+(LEFT(RC[-1])=""0""),99)"
ActiveCell.Offset(1, -1).Select
End If
Next cell
Next X
'Loop
End Sub


HTH,
Ryan---
 
S

Steved

Hello ryguy7272

ryguy7272 the below line is producing =MID(A2,1+(LEFT(A2)="0"),99)

ActiveCell.FormulaR1C1 = "=MID(RC[-1],1+(LEFT(RC[-1])=""0""),99)"

I'm not shore what to do for it be =MID(B2,1+(LEFT(B2)="0"),99)

Please I thanyou for your patience on my issue.




ryguy7272 said:
There are MANY ways to do this. This may be one of the more intuitive ways;
hopefully easy to interpret the logic:

Sub DelZeros()
'Do Until ActiveCell = ""
For X = 1 To 1
Dim Rng As Range
Set Rng = Range("B2", Range("B56000").End(xlUp))
For Each cell In Rng
If cell.Value <> "" Then
ActiveCell.Offset(0, 1).Select
Click to expand...
ActiveCell.FormulaR1C1 = "=MID(RC[-1],1+(LEFT(RC[-1])=""0""),99)"
ActiveCell.Offset(1, -1).Select
 
R

ryguy7272

Those pesky double quotes...

Try this:
ActiveCell.FormulaR1C1 = "=MID(RC[-1],1+(LEFT(RC[-1])="0"),99)"


HTH,
Ryan---
--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.


Steved said:
Hello ryguy7272

ryguy7272 the below line is producing =MID(A2,1+(LEFT(A2)="0"),99)

ActiveCell.FormulaR1C1 = "=MID(RC[-1],1+(LEFT(RC[-1])=""0""),99)"

I'm not shore what to do for it be =MID(B2,1+(LEFT(B2)="0"),99)

Please I thanyou for your patience on my issue.




ryguy7272 said:
There are MANY ways to do this. This may be one of the more intuitive ways;
hopefully easy to interpret the logic:

Sub DelZeros()
'Do Until ActiveCell = ""
For X = 1 To 1
Dim Rng As Range
Set Rng = Range("B2", Range("B56000").End(xlUp))
For Each cell In Rng
If cell.Value <> "" Then
ActiveCell.Offset(0, 1).Select
Click to expand...
ActiveCell.FormulaR1C1 = "=MID(RC[-1],1+(LEFT(RC[-1])=""0""),99)"
ActiveCell.Offset(1, -1).Select
End If
Next cell
Next X
'Loop
End Sub


HTH,
Ryan---
Click to expand...
Click to expand...
 
S

Steved

hello again

you are asking to go from this to
ActiveCell.FormulaR1C1 = "=MID(RC[-1],1+(LEFT(RC[-1])=""0""),99)"

this
ActiveCell.FormulaR1C1 = "=MID(RC[-1],1+(LEFT(RC[-1])="0"),99)"

I'm receiving a compile error "Expected: end of statement" when I removed ""

we are getting their.



ryguy7272 said:
Those pesky double quotes...

Try this:
ActiveCell.FormulaR1C1 = "=MID(RC[-1],1+(LEFT(RC[-1])="0"),99)"


HTH,
Ryan---
--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.


Steved said:
Hello ryguy7272

ryguy7272 the below line is producing =MID(A2,1+(LEFT(A2)="0"),99)

ActiveCell.FormulaR1C1 = "=MID(RC[-1],1+(LEFT(RC[-1])=""0""),99)"

I'm not shore what to do for it be =MID(B2,1+(LEFT(B2)="0"),99)

Please I thanyou for your patience on my issue.




ryguy7272 said:
There are MANY ways to do this. This may be one of the more intuitive ways;
hopefully easy to interpret the logic:

Sub DelZeros()
'Do Until ActiveCell = ""
For X = 1 To 1
Dim Rng As Range
Set Rng = Range("B2", Range("B56000").End(xlUp))
For Each cell In Rng
If cell.Value <> "" Then
ActiveCell.Offset(0, 1).Select
Click to expand...
ActiveCell.FormulaR1C1 = "=MID(RC[-1],1+(LEFT(RC[-1])=""0""),99)"
ActiveCell.Offset(1, -1).Select
End If
Next cell
Next X
'Loop
End Sub


HTH,
Ryan---
--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.


:

Hello from Steved

Col B:B
Column format is General
0702301 to become 702301 but if it has an X702301 please ignore
Yes I'm only requiring the first digit of the cell to be changed ie delete
"0" if it has an "X" ignore it and find the next "0"

The bottom is similar as to what I'm trying to acheive please
Sub removezero()
Range("B2:B250").Formula = "=required Formula please"
End Sub

Thankyou
Click to expand...
Click to expand...
Click to expand...
 
S

Steved

hello again

Removing the quotes gives an compile error:endstatement error,

We are nearly their so hopefully you can solve this for me.

Once again I thankyou for timeout on my issue.


ryguy7272 said:
Those pesky double quotes...

Try this:
ActiveCell.FormulaR1C1 = "=MID(RC[-1],1+(LEFT(RC[-1])="0"),99)"


HTH,
Ryan---
--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.


Steved said:
Hello ryguy7272

ryguy7272 the below line is producing =MID(A2,1+(LEFT(A2)="0"),99)

ActiveCell.FormulaR1C1 = "=MID(RC[-1],1+(LEFT(RC[-1])=""0""),99)"

I'm not shore what to do for it be =MID(B2,1+(LEFT(B2)="0"),99)

Please I thanyou for your patience on my issue.




ryguy7272 said:
There are MANY ways to do this. This may be one of the more intuitive ways;
hopefully easy to interpret the logic:

Sub DelZeros()
'Do Until ActiveCell = ""
For X = 1 To 1
Dim Rng As Range
Set Rng = Range("B2", Range("B56000").End(xlUp))
For Each cell In Rng
If cell.Value <> "" Then
ActiveCell.Offset(0, 1).Select
Click to expand...
ActiveCell.FormulaR1C1 = "=MID(RC[-1],1+(LEFT(RC[-1])=""0""),99)"
ActiveCell.Offset(1, -1).Select
End If
Next cell
Next X
'Loop
End Sub


HTH,
Ryan---
--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.


:

Hello from Steved

Col B:B
Column format is General
0702301 to become 702301 but if it has an X702301 please ignore
Yes I'm only requiring the first digit of the cell to be changed ie delete
"0" if it has an "X" ignore it and find the next "0"

The bottom is similar as to what I'm trying to acheive please
Sub removezero()
Range("B2:B250").Formula = "=required Formula please"
End Sub

Thankyou
Click to expand...
Click to expand...
Click to expand...
 
R

ryguy7272

Sorry, didn't test it before; just threw it out there. I just tried the
version below and it worked fine for me:

Sub DelZeros()
'Do Until ActiveCell = ""
For X = 1 To 1
Dim Rng As Range
Set Rng = Range("B2", Range("B56000").End(xlUp))
Range("B2").Select
For Each cell In Rng
If cell.Value <> "" Then
ActiveCell.Offset(0, 1).Select
ActiveCell.FormulaR1C1 = "=MID(RC[-1],1+(LEFT(RC[-1])=""0""),99)"
ActiveCell.Offset(1, -1).Select
End If
Next cell
Next X
'Loop
End Sub

HTH,
Ryan---

--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.


Steved said:
hello again

Removing the quotes gives an compile error:endstatement error,

We are nearly their so hopefully you can solve this for me.

Once again I thankyou for timeout on my issue.


ryguy7272 said:
Those pesky double quotes...

Try this:
ActiveCell.FormulaR1C1 = "=MID(RC[-1],1+(LEFT(RC[-1])="0"),99)"


HTH,
Ryan---
--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.


Steved said:
Hello ryguy7272

ryguy7272 the below line is producing =MID(A2,1+(LEFT(A2)="0"),99)

ActiveCell.FormulaR1C1 = "=MID(RC[-1],1+(LEFT(RC[-1])=""0""),99)"

I'm not shore what to do for it be =MID(B2,1+(LEFT(B2)="0"),99)

Please I thanyou for your patience on my issue.




:

There are MANY ways to do this. This may be one of the more intuitive ways;
hopefully easy to interpret the logic:

Sub DelZeros()
'Do Until ActiveCell = ""
For X = 1 To 1
Dim Rng As Range
Set Rng = Range("B2", Range("B56000").End(xlUp))
For Each cell In Rng
If cell.Value <> "" Then
ActiveCell.Offset(0, 1).Select
ActiveCell.FormulaR1C1 = "=MID(RC[-1],1+(LEFT(RC[-1])=""0""),99)"
ActiveCell.Offset(1, -1).Select
End If
Next cell
Next X
'Loop
End Sub


HTH,
Ryan---
--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.


:

Hello from Steved

Col B:B
Column format is General
0702301 to become 702301 but if it has an X702301 please ignore
Yes I'm only requiring the first digit of the cell to be changed ie delete
"0" if it has an "X" ignore it and find the next "0"

The bottom is similar as to what I'm trying to acheive please
Sub removezero()
Range("B2:B250").Formula = "=required Formula please"
End Sub

Thankyou
Click to expand...
Click to expand...
Click to expand...
 
S

Steved

Hello ryguy7272 from Steved

Thankyou excellent value




ryguy7272 said:
Sorry, didn't test it before; just threw it out there. I just tried the
version below and it worked fine for me:

Sub DelZeros()
'Do Until ActiveCell = ""
For X = 1 To 1
Dim Rng As Range
Set Rng = Range("B2", Range("B56000").End(xlUp))
Range("B2").Select
For Each cell In Rng
If cell.Value <> "" Then
ActiveCell.Offset(0, 1).Select
ActiveCell.FormulaR1C1 = "=MID(RC[-1],1+(LEFT(RC[-1])=""0""),99)"
ActiveCell.Offset(1, -1).Select
End If
Next cell
Next X
'Loop
End Sub

HTH,
Ryan---

--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.


Steved said:
hello again

Removing the quotes gives an compile error:endstatement error,

We are nearly their so hopefully you can solve this for me.

Once again I thankyou for timeout on my issue.


ryguy7272 said:
Those pesky double quotes...

Try this:
ActiveCell.FormulaR1C1 = "=MID(RC[-1],1+(LEFT(RC[-1])="0"),99)"


HTH,
Ryan---
--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.


:

Hello ryguy7272

ryguy7272 the below line is producing =MID(A2,1+(LEFT(A2)="0"),99)

ActiveCell.FormulaR1C1 = "=MID(RC[-1],1+(LEFT(RC[-1])=""0""),99)"

I'm not shore what to do for it be =MID(B2,1+(LEFT(B2)="0"),99)

Please I thanyou for your patience on my issue.




:

There are MANY ways to do this. This may be one of the more intuitive ways;
hopefully easy to interpret the logic:

Sub DelZeros()
'Do Until ActiveCell = ""
For X = 1 To 1
Dim Rng As Range
Set Rng = Range("B2", Range("B56000").End(xlUp))
For Each cell In Rng
If cell.Value <> "" Then
ActiveCell.Offset(0, 1).Select
ActiveCell.FormulaR1C1 = "=MID(RC[-1],1+(LEFT(RC[-1])=""0""),99)"
ActiveCell.Offset(1, -1).Select
End If
Next cell
Next X
'Loop
End Sub


HTH,
Ryan---
--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.


:

Hello from Steved

Col B:B
Column format is General
0702301 to become 702301 but if it has an X702301 please ignore
Yes I'm only requiring the first digit of the cell to be changed ie delete
"0" if it has an "X" ignore it and find the next "0"

The bottom is similar as to what I'm trying to acheive please
Sub removezero()
Range("B2:B250").Formula = "=required Formula please"
End Sub

Thankyou
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Only remove first digit if it is a "0" 4
Vlookup in VBA will only find first match??? 2
VBA Updates Cell but not the cells that reference it with formulas 2
Using Sheets.Add after and want to keep a sheet as the first sheet 2
Updating Files 5
Win 7 ultimate iso info--help me decipher this pls - piratedsoftware? Not. 18
FWT Newsletter - Weekly - October 25, 2004 8
FWT Newsletter - Weekly - August 16, 2004 1

Top