Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
using VBA remove "0" but leave "X" first digit only please.
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
using VBA remove "0" but leave "X" first digit only please.
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 "Steved" wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
using VBA remove "0" but leave "X" first digit only please.
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''. "Steved" wrote: 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
using VBA remove "0" but leave "X" first digit only please.
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" wrote: 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''. "Steved" wrote: 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
using VBA remove "0" but leave "X" first digit only please.
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" wrote: 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" wrote: 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''. "Steved" wrote: 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
using VBA remove "0" but leave "X" first digit only please.
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" wrote: 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" wrote: 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" wrote: 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''. "Steved" wrote: 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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
using VBA remove "0" but leave "X" first digit only please.
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" wrote: 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" wrote: 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" wrote: 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''. "Steved" wrote: 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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
using VBA remove "0" but leave "X" first digit only please.
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" wrote: 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" wrote: 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" wrote: 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" wrote: 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''. "Steved" wrote: 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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
using VBA remove "0" but leave "X" first digit only please.
Hello ryguy7272 from Steved
Thankyou excellent value "ryguy7272" wrote: 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" wrote: 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" wrote: 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" wrote: 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" wrote: 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''. "Steved" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel - Golf - how to display "-2" as "2 Under" or "4"as "+4" or "4 Over" in a calculation cell | Excel Discussion (Misc queries) | |||
change "true" and "false" to "availble" and "out of stock" | Excel Worksheet Functions | |||
HELP on "left","right","find","len","substitute" functions | Excel Discussion (Misc queries) | |||
Count occurences of "1"/"0" (or"TRUE"/"FALSE") in a row w. conditions in the next | New Users to Excel | |||
If changed array formula reduce ""\""\""\ - signs to #Missing, will it make ... | Excel Programming |