My attempt to do a Replace

S

Steved

Hello from Steved

Whats my Objective

simply replace Semi colon (":")

example 07:40 to 0740 or 13:05 to 1305

The information is in Column C:C and I've asking it to be put in Column D:D
please

Sub ReplaceColon()
For X = 1 To 1
Dim Rng As Range
Set Rng = Format(Range("C2"), "hhmm").End(xlUp)
Range("C2").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
End Sub

Thankyou
 
J

Jacob Skaria

In D1 use a formula and copy that down
=TEXT(C1,"hhmm")

OR

Sub Mac()
lngLastRow = ActiveSheet.Cells(Rows.Count, "C").End(xlUp).Row
For lngRow = 1 To lngLastRow
Range("D" & lngRow) = Format(Range("c" & lngRow), "hhmm")
Next
End Sub


If this post helps click Yes
 
J

Joel

Sub ReplaceColon()

Dim Rng As Range
LastRow = Range("C" & Rows.Count).End(xlUp).Row
Range("D2").Formula = "=text(C2,""HHMM"")"
Range("D2").Copy _
Destination:=Range("D2:D" & LastRow)

End Sub
 
S

Steved

Hello Jacob from Steved

I'm getting a Compile error "Variable Not Defined"

your script is highlighting "lngLastRow ="


Is this because in the Formula Bar 13:05 is displayed as 1:05:00 p.m.

I'm very sorry this is beyond my understanding as to what is happening and I
thank you for taking timeout on my issue.




Jacob Skaria said:
In D1 use a formula and copy that down
=TEXT(C1,"hhmm")

OR

Sub Mac()
lngLastRow = ActiveSheet.Cells(Rows.Count, "C").End(xlUp).Row
For lngRow = 1 To lngLastRow
Range("D" & lngRow) = Format(Range("c" & lngRow), "hhmm")
Next
End Sub


If this post helps click Yes
---------------
Jacob Skaria


Steved said:
Hello from Steved

Whats my Objective

simply replace Semi colon (":")

example 07:40 to 0740 or 13:05 to 1305

The information is in Column C:C and I've asking it to be put in Column D:D
please

Sub ReplaceColon()
For X = 1 To 1
Dim Rng As Range
Set Rng = Format(Range("C2"), "hhmm").End(xlUp)
Range("C2").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
End Sub

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

Steved

Hello Joel from Steved

Joel your script is showing a Compile error "Variable not defined"

In your case it is highlighting "LastRow ="

Is this because 13:05 in the worksheet, is displayed as 1:05:00 p.m. in the
Formula Bar.

I thankyou for taking timeout on my issue.



Joel said:
Sub ReplaceColon()

Dim Rng As Range
LastRow = Range("C" & Rows.Count).End(xlUp).Row
Range("D2").Formula = "=text(C2,""HHMM"")"
Range("D2").Copy _
Destination:=Range("D2:D" & LastRow)

End Sub


Steved said:
Hello from Steved

Whats my Objective

simply replace Semi colon (":")

example 07:40 to 0740 or 13:05 to 1305

The information is in Column C:C and I've asking it to be put in Column D:D
please

Sub ReplaceColon()
For X = 1 To 1
Dim Rng As Range
Set Rng = Format(Range("C2"), "hhmm").End(xlUp)
Range("C2").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
End Sub

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

Joel

add the declaration

Dim LastRow as Long


Steved said:
Hello Joel from Steved

Joel your script is showing a Compile error "Variable not defined"

In your case it is highlighting "LastRow ="

Is this because 13:05 in the worksheet, is displayed as 1:05:00 p.m. in the
Formula Bar.

I thankyou for taking timeout on my issue.



Joel said:
Sub ReplaceColon()

Dim Rng As Range
LastRow = Range("C" & Rows.Count).End(xlUp).Row
Range("D2").Formula = "=text(C2,""HHMM"")"
Range("D2").Copy _
Destination:=Range("D2:D" & LastRow)

End Sub


Steved said:
Hello from Steved

Whats my Objective

simply replace Semi colon (":")

example 07:40 to 0740 or 13:05 to 1305

The information is in Column C:C and I've asking it to be put in Column D:D
please

Sub ReplaceColon()
For X = 1 To 1
Dim Rng As Range
Set Rng = Format(Range("C2"), "hhmm").End(xlUp)
Range("C2").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
End Sub

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

Steved

Hello Joel

Thankyou very much.

With your script is it possible to do replace in the same column ie Column
C:\ please.


Joel said:
add the declaration

Dim LastRow as Long


Steved said:
Hello Joel from Steved

Joel your script is showing a Compile error "Variable not defined"

In your case it is highlighting "LastRow ="

Is this because 13:05 in the worksheet, is displayed as 1:05:00 p.m. in the
Formula Bar.

I thankyou for taking timeout on my issue.



Joel said:
Sub ReplaceColon()

Dim Rng As Range
LastRow = Range("C" & Rows.Count).End(xlUp).Row
Range("D2").Formula = "=text(C2,""HHMM"")"
Range("D2").Copy _
Destination:=Range("D2:D" & LastRow)

End Sub


:

Hello from Steved

Whats my Objective

simply replace Semi colon (":")

example 07:40 to 0740 or 13:05 to 1305

The information is in Column C:C and I've asking it to be put in Column D:D
please

Sub ReplaceColon()
For X = 1 To 1
Dim Rng As Range
Set Rng = Format(Range("C2"), "hhmm").End(xlUp)
Range("C2").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
End Sub

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

Joel

Yes and no. You can put the formula in an unused column like IV. Then paste
the values back into column C. see changes below

Sub ReplaceColon()

Dim Rng As Range
Dim LastRow as long
LastRow = Range("C" & Rows.Count).End(xlUp).Row
'put formula in column IV and copy down column
Range("IV2").Formula = "=text(C2,""HHMM"")"
Range("IV2").Copy _
Destination:=Range("IV2:IV" & LastRow)

'pastespecial values back into column C
Range("IV2:IV" & LastRow).Copy
Range("C2").pasteSpecial _
paste:=xlpastevalues

'delete column IV
columns("IV").delete
End Sub



Steved said:
Hello Joel

Thankyou very much.

With your script is it possible to do replace in the same column ie Column
C:\ please.


Joel said:
add the declaration

Dim LastRow as Long


Steved said:
Hello Joel from Steved

Joel your script is showing a Compile error "Variable not defined"

In your case it is highlighting "LastRow ="

Is this because 13:05 in the worksheet, is displayed as 1:05:00 p.m. in the
Formula Bar.

I thankyou for taking timeout on my issue.



:

Sub ReplaceColon()

Dim Rng As Range
LastRow = Range("C" & Rows.Count).End(xlUp).Row
Range("D2").Formula = "=text(C2,""HHMM"")"
Range("D2").Copy _
Destination:=Range("D2:D" & LastRow)

End Sub


:

Hello from Steved

Whats my Objective

simply replace Semi colon (":")

example 07:40 to 0740 or 13:05 to 1305

The information is in Column C:C and I've asking it to be put in Column D:D
please

Sub ReplaceColon()
For X = 1 To 1
Dim Rng As Range
Set Rng = Format(Range("C2"), "hhmm").End(xlUp)
Range("C2").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
End Sub

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

Steved

Hello Joel

Excellent and I thankyou.

Steved


Joel said:
Yes and no. You can put the formula in an unused column like IV. Then paste
the values back into column C. see changes below

Sub ReplaceColon()

Dim Rng As Range
Dim LastRow as long
LastRow = Range("C" & Rows.Count).End(xlUp).Row
'put formula in column IV and copy down column
Range("IV2").Formula = "=text(C2,""HHMM"")"
Range("IV2").Copy _
Destination:=Range("IV2:IV" & LastRow)

'pastespecial values back into column C
Range("IV2:IV" & LastRow).Copy
Range("C2").pasteSpecial _
paste:=xlpastevalues

'delete column IV
columns("IV").delete
End Sub



Steved said:
Hello Joel

Thankyou very much.

With your script is it possible to do replace in the same column ie Column
C:\ please.


Joel said:
add the declaration

Dim LastRow as Long


:

Hello Joel from Steved

Joel your script is showing a Compile error "Variable not defined"

In your case it is highlighting "LastRow ="

Is this because 13:05 in the worksheet, is displayed as 1:05:00 p.m. in the
Formula Bar.

I thankyou for taking timeout on my issue.



:

Sub ReplaceColon()

Dim Rng As Range
LastRow = Range("C" & Rows.Count).End(xlUp).Row
Range("D2").Formula = "=text(C2,""HHMM"")"
Range("D2").Copy _
Destination:=Range("D2:D" & LastRow)

End Sub


:

Hello from Steved

Whats my Objective

simply replace Semi colon (":")

example 07:40 to 0740 or 13:05 to 1305

The information is in Column C:C and I've asking it to be put in Column D:D
please

Sub ReplaceColon()
For X = 1 To 1
Dim Rng As Range
Set Rng = Format(Range("C2"), "hhmm").End(xlUp)
Range("C2").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
End Sub

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

Patrick Molloy

add the two lines as shown :-
DIM lngLastRow As Long ' ADD THIS
DIM lngRow As Long ' ADD THIS
Steved said:
Hello Jacob from Steved

I'm getting a Compile error "Variable Not Defined"

your script is highlighting "lngLastRow ="


Is this because in the Formula Bar 13:05 is displayed as 1:05:00 p.m.

I'm very sorry this is beyond my understanding as to what is happening and
I
thank you for taking timeout on my issue.




Jacob Skaria said:
In D1 use a formula and copy that down
=TEXT(C1,"hhmm")

OR

Sub Mac()
lngLastRow = ActiveSheet.Cells(Rows.Count, "C").End(xlUp).Row
For lngRow = 1 To lngLastRow
Range("D" & lngRow) = Format(Range("c" & lngRow), "hhmm")
Next
End Sub


If this post helps click Yes
---------------
Jacob Skaria


Steved said:
Hello from Steved

Whats my Objective

simply replace Semi colon (":")

example 07:40 to 0740 or 13:05 to 1305

The information is in Column C:C and I've asking it to be put in Column
D:D
please

Sub ReplaceColon()
For X = 1 To 1
Dim Rng As Range
Set Rng = Format(Range("C2"), "hhmm").End(xlUp)
Range("C2").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
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

Remove a "0" 9
how to use Solver in Excel VBA 1
Word macro that can call cells out of Excel for a find and replace 0
Copy filtered data to sheet 2 1
My macro doesn't work if sheet is protected but it works if not protected 2
Data samples 3
Macro help 0
works in 2007 but not in 2010 0

Top