Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > My attempt to do a Replace
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 519
Default My attempt to do a Replace

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



Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 8,520
Default My attempt to do a Replace

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" wrote:

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



Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 9,101
Default My attempt to do a Replace

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" wrote:

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



Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 519
Default My attempt to do a Replace
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" wrote:

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" wrote:

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




-----------------------------------------------------------------------------
Our Peering Groups change
Visit : http://spacesst.com/peerin
Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 519
Default My attempt to do a Replace
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" wrote:

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" wrote:

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




-----------------------------------------------------------------------------
Our Peering Groups change
Visit : http://spacesst.com/peerin
Reply With QuoteReply With Quote
  #6   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 9,101
Default My attempt to do a Replace
add the declaration

Dim LastRow as Long


"Steved" wrote:

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" wrote:

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" wrote:

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




-----------------------------------------------------------------------------
Less Spam Better enjoyable experience
Visit : news://spacesst.com
Reply With QuoteReply With Quote
  #7   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 519
Default My attempt to do a Replace
Hello Joel

Thankyou very much.

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


"Joel" wrote:

add the declaration

Dim LastRow as Long


"Steved" wrote:

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" wrote:

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" wrote:

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




-----------------------------------------------------------------------------
Less Spam Better enjoyable experience
Visit : news://spacesst.com
Reply With QuoteReply With Quote
  #8   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 9,101
Default My attempt to do a Replace
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" wrote:

Hello Joel

Thankyou very much.

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


"Joel" wrote:

add the declaration

Dim LastRow as Long


"Steved" wrote:

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" wrote:

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" wrote:

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




-----------------------------------------------------------------------------
Less Spam Better enjoyable experience
Visit : news://spacesst.com
Reply With QuoteReply With Quote
  #9   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 519
Default My attempt to do a Replace
Hello Joel

Excellent and I thankyou.

Steved


"Joel" wrote:

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" wrote:

Hello Joel

Thankyou very much.

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


"Joel" wrote:

add the declaration

Dim LastRow as Long


"Steved" wrote:

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" wrote:

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" wrote:

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




-----------------------------------------------------------------------------
Less Spam Better enjoyable experience
Visit : news://spacesst.com
Reply With QuoteReply With Quote
  #10   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 1,049
Default My attempt to do a Replace

add the two lines as shown :-

Sub Mac()
DIM lngLastRow As Long ' ADD THIS
DIM lngRow As Long ' ADD THIS
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

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

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" wrote:

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



Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
Need Help in VLOOKUP (second attempt) Dan Excel Programming 8 June 2nd 08 09:44 PM
Sumproduct - Second Attempt Sandy Excel Worksheet Functions 4 August 10th 07 06:02 PM
Do until code attempt.. hachiroku[_3_] Excel Programming 2 May 20th 06 01:46 PM
my first attempt at R1C1 in vba [email protected] Excel Programming 4 September 5th 05 04:32 PM
1st attempt vba-how do i whatnext in this sub ALAN EMERY Excel Programming 1 July 27th 04 06:11 PM


All times are GMT +1. The time now is 04:06 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement