Macro - code for ranges urgent!!!
 

Hi

I have created a macro which will move columns and put them where i
want them in my final spreadsheet and in 2 particular columns b & d i
have a written in the macro to convert time into an integer.

However, i need the macro to work just on the cells containing info in
the particular columns, because after it runs i end up with loads of
zero's in the columns. There is not a typical range as this is
dependent upon the number of records in the work sheet.

I would appreciate any help.

this is the relevant part of the code:

range("AP2").Select
ActiveWindow.ScrollColumn = 1
range("A2").Select


range("B:B,D:D").Select
range("D1").Activate

Dim c As range
For Each c In Selection
If IsNumeric(c.Value) Then
c.Value = Int(c.Value * 24) * 100 + _
Int(60 * (c.Value * 24 - Int(c.Value * 24)))
c.NumberFormat = "0000"
End If
Next c
End Sub


---
Message posted from http://www.ExcelForum.com/

Macro - code for ranges urgent!!!
 

Hi
replace the line
If IsNumeric(c.Value) Then
with
If IsNumeric(c.Value) and c.value<"" Then


--
Regards
Frank Kabel
Frankfurt, Germany

Hi

I have created a macro which will move columns and put them where i
want them in my final spreadsheet and in 2 particular columns b & d i
have a written in the macro to convert time into an integer.

However, i need the macro to work just on the cells containing info
in
the particular columns, because after it runs i end up with loads of
zero's in the columns. There is not a typical range as this is
dependent upon the number of records in the work sheet.

I would appreciate any help.

this is the relevant part of the code:

range("AP2").Select
ActiveWindow.ScrollColumn = 1
range("A2").Select


range("B:B,D:D").Select
range("D1").Activate

Dim c As range
For Each c In Selection
If IsNumeric(c.Value) Then
c.Value = Int(c.Value * 24) * 100 + _
Int(60 * (c.Value * 24 - Int(c.Value * 24)))
c.NumberFormat = "0000"
End If
Next c
End Sub


---
Message posted from http://www.ExcelForum.com/

Macro - code for ranges urgent!!!
 

And removal of extraneous code

Dim c As Range
For Each c In Range("B:B,D:D")
If Not IsEmpty(c.Value) Then
If IsNumeric(c.Value) Then
c.Value = Int(c.Value * 24) * 100 + _
Int(60 * (c.Value * 24 - Int(c.Value * 24)))
c.NumberFormat = "0000"
End If
End If
Next c

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"rjc_29 " wrote in message
...
Hi

I have created a macro which will move columns and put them where i
want them in my final spreadsheet and in 2 particular columns b & d i
have a written in the macro to convert time into an integer.

However, i need the macro to work just on the cells containing info in
the particular columns, because after it runs i end up with loads of
zero's in the columns. There is not a typical range as this is
dependent upon the number of records in the work sheet.

I would appreciate any help.

this is the relevant part of the code:

range("AP2").Select
ActiveWindow.ScrollColumn = 1
range("A2").Select


range("B:B,D:D").Select
range("D1").Activate

Dim c As range
For Each c In Selection
If IsNumeric(c.Value) Then
c.Value = Int(c.Value * 24) * 100 + _
Int(60 * (c.Value * 24 - Int(c.Value * 24)))
c.NumberFormat = "0000"
End If
Next c
End Sub


---
Message posted from http://www.ExcelForum.com/

Macro - code for ranges urgent!!!
 

Frank

Thanks for your help - it worked!

regards

Rom

--
Message posted from http://www.ExcelForum.com