Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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/ |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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/ |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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/ |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro - code for ranges urgent!!!
Frank
Thanks for your help - it worked! regards Rom -- Message posted from http://www.ExcelForum.com |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
URGENT: Using Named Ranges with Chart Wizard | Excel Worksheet Functions | |||
URGENT - Need date format for SaveAs code | Excel Programming | |||
Urgent questions regarding the code below | Excel Programming | |||
Macro help urgent urgent | Excel Programming | |||
Macro help urgent urgent | Excel Programming |