Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Multiple For/Next
I'm trying to run the following code for each cell in range C2:Q16 ActiveCell.NumberFormat = "General" ActiveCell.Value = ActiveCell.Value I've put this as a loop, but would like to run it for rows C to without having to put in Range("c" & I)...Range("d" & I)....ect For I = 2 To 16 Range("C" & I).Select ActiveCell.NumberFormat = "General" ActiveCell.Value = ActiveCell.Value next I tried Range(& X & I).select, but it doesn't work. Where x = c to Q There is probably a way to select a range C2:Q16 and then convert. Ca anyone help? I'd also like to know how to use two "For" statements i combination, just for future reference. Thanks guy -- Ramthebuff ----------------------------------------------------------------------- Ramthebuffs's Profile: http://www.excelforum.com/member.php...fo&userid=1642 View this thread: http://www.excelforum.com/showthread.php?threadid=48295 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Multiple For/Next
Try something like
Dim Rng As Range For Each Rng In Range("C2:Q16") Rng.NumberFormat = "General" Rng.Value = Rng.Value Next Rng -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "Ramthebuffs" wrote in message ... I'm trying to run the following code for each cell in range C2:Q16 ActiveCell.NumberFormat = "General" ActiveCell.Value = ActiveCell.Value I've put this as a loop, but would like to run it for rows C to Q without having to put in Range("c" & I)...Range("d" & I)....ect For I = 2 To 16 Range("C" & I).Select ActiveCell.NumberFormat = "General" ActiveCell.Value = ActiveCell.Value next I tried Range(& X & I).select, but it doesn't work. Where x = c to Q There is probably a way to select a range C2:Q16 and then convert. Can anyone help? I'd also like to know how to use two "For" statements in combination, just for future reference. Thanks guys -- Ramthebuffs ------------------------------------------------------------------------ Ramthebuffs's Profile: http://www.excelforum.com/member.php...o&userid=16429 View this thread: http://www.excelforum.com/showthread...hreadid=482958 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Multiple For/Next
Hello Ramthebuffs, This should do it. You don't need to reassign the cell value in th loop. You are only changing the format. Code ------------------- Dim c Dim Rng As Range Set rng = ActiveSheet.Range("C2:Q16") For Each c In Rng c.NumberFormat = "General" Next c ------------------- Sincerely, Leith Ros -- Leith Ros ----------------------------------------------------------------------- Leith Ross's Profile: http://www.excelforum.com/member.php...fo&userid=1846 View this thread: http://www.excelforum.com/showthread.php?threadid=48295 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Multiple For/Next
You could try:
With Range("C2:Q16") .NumberFormat = "General" .Value = .Value End With Hope this helps Rowan Ramthebuffs wrote: I'm trying to run the following code for each cell in range C2:Q16 ActiveCell.NumberFormat = "General" ActiveCell.Value = ActiveCell.Value I've put this as a loop, but would like to run it for rows C to Q without having to put in Range("c" & I)...Range("d" & I)....ect For I = 2 To 16 Range("C" & I).Select ActiveCell.NumberFormat = "General" ActiveCell.Value = ActiveCell.Value next I tried Range(& X & I).select, but it doesn't work. Where x = c to Q There is probably a way to select a range C2:Q16 and then convert. Can anyone help? I'd also like to know how to use two "For" statements in combination, just for future reference. Thanks guys |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Multiple For/Next
Hi,
you can combine 2 for-loops for i=1 to 10 for j=1 to 10 cells(i,j).numberformat = "General" cells(i,j).value = "whatever" next next i = rows j = columns Greetings Jos Vens "Ramthebuffs" schreef in bericht ... I'm trying to run the following code for each cell in range C2:Q16 ActiveCell.NumberFormat = "General" ActiveCell.Value = ActiveCell.Value I've put this as a loop, but would like to run it for rows C to Q without having to put in Range("c" & I)...Range("d" & I)....ect For I = 2 To 16 Range("C" & I).Select ActiveCell.NumberFormat = "General" ActiveCell.Value = ActiveCell.Value next I tried Range(& X & I).select, but it doesn't work. Where x = c to Q There is probably a way to select a range C2:Q16 and then convert. Can anyone help? I'd also like to know how to use two "For" statements in combination, just for future reference. Thanks guys -- Ramthebuffs ------------------------------------------------------------------------ Ramthebuffs's Profile: http://www.excelforum.com/member.php...o&userid=16429 View this thread: http://www.excelforum.com/showthread...hreadid=482958 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Multiple For/Next
Just replace 'ActiveCell' with 'Selection', thus:
With Selection .Numberformat="General" .Value= "Whatever" End With and it will do it for the whole selected area at once. alternatively, if you don't want to have to select the area of interest first, just specify it a range ie replace 'Selection' with 'Range("C2:Q16")'. "Jos Vens" wrote: Hi, you can combine 2 for-loops for i=1 to 10 for j=1 to 10 cells(i,j).numberformat = "General" cells(i,j).value = "whatever" next next i = rows j = columns Greetings Jos Vens "Ramthebuffs" schreef in bericht ... I'm trying to run the following code for each cell in range C2:Q16 ActiveCell.NumberFormat = "General" ActiveCell.Value = ActiveCell.Value I've put this as a loop, but would like to run it for rows C to Q without having to put in Range("c" & I)...Range("d" & I)....ect For I = 2 To 16 Range("C" & I).Select ActiveCell.NumberFormat = "General" ActiveCell.Value = ActiveCell.Value next I tried Range(& X & I).select, but it doesn't work. Where x = c to Q There is probably a way to select a range C2:Q16 and then convert. Can anyone help? I'd also like to know how to use two "For" statements in combination, just for future reference. Thanks guys -- Ramthebuffs ------------------------------------------------------------------------ Ramthebuffs's Profile: http://www.excelforum.com/member.php...o&userid=16429 View this thread: http://www.excelforum.com/showthread...hreadid=482958 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how to link multiple columns to multiple worksheet totals / functi | Excel Worksheet Functions | |||
Count multiple cells against multiple criteria in an Excel spreads | Excel Worksheet Functions | |||
Delete Blank Rows Code - Multiple Worksheets - Multiple Documents | Excel Discussion (Misc queries) | |||
Date and time stamping multiple cells for multiple entries. | Excel Worksheet Functions | |||
view multiple files in multiple windows on multiple screens. | Excel Discussion (Misc queries) |