Using a for loop in excel sheet

N

Nitin Sadana

New Member
Joined
May 23, 2009
Messages
5
Hello All,
Please tell me if how can i use a for loop in an excel sheet. For eg.
A formula should check C1 to C500 and then if the condition matches print those values in B1 to B10

Please guide me

Rgds,
Nitin
 

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying
Sort by date Sort by votes
Hi,

Something like this maybe;

Code: 
Sub Test()

For i = 1 To 500
    If Cells(i, 3) = "Condition" Then Cells(i, 2) = Cells(i, 3)
Next i

End Sub
 
Upvote 0
not sure what you are trying to do...but this would loop through each cell in col C and put that same value in column B...

Code: 
Sub loop()
  For Each cell In Range("C1:C500")
     cell.Offset(0, -1).Value = cell.Value
  Next cell
End Sub
 
Upvote 0
I guess another question is, is it always 500 rows or does it vary? This would work with a variable amount of data.

Code: 
Sub Test()

Dim lRow As Long

lRow = Range("C" & Rows.Count).End(xlUp).Row

For i = 1 To lRow
    If Cells(i, 3) = "Condition" Then Cells(i, 2) = Cells(i, 3)
Next i

End Sub
 
Upvote 0
Mike Blackman said:
I guess another question is, is it always 500 rows or does it vary? This would work with a variable amount of data.

Code: 
Sub Test()
 
Dim lRow As Long
 
lRow = Range("C" & Rows.Count).End(xlUp).Row
 
For i = 1 To lRow
    If Cells(i, 3) = "Condition" Then Cells(i, 2) = Cells(i, 3)
Next i
 
End Sub
Click to expand...


Yes it varies.... Actually my exact requirement is....
There is certain(fixed) amount of data that is updated everyday by my collegues and i want only yesterday's data to be printed as i come to the office...
Daily a lot of time is wasted in selecting rows that pertains to previous day...so i thought that if there can be some macro that could match with date and print the required data... and yes the total rows vary...

i hope am clear now... Please Guide me
thanks in advance
 
Last edited:
Upvote 0
Nitin Sadana said:
Yes it varies.... Actually my exact requirement is....
There is certain(fixed) amount of data that is updated everyday by my collegues and i want only yesterday's data to be printed as i come to the office...
Daily a lot of time is wasted in selecting rows that pertains to previous day...so i thought that if there can be some macro that could match with date and print the required data... and yes the total rows vary...

i hope am clear now... Please Guide me
thanks in advance
Click to expand...


I am presently using this code for the above requirement but its showing some error :(

Sub Post()

Sheets("Final Data").Select
Dim lRow As Long
lRow = Range("B" & Rows.Count).End(xlUp).Row
For i = 1 To lRow
If Cells(i, 2) = "Today()" Then
Range("Fi:Si").Select
Selection.Copy

Sheets("Scorecard").Select
j = 2
Range("Bj").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
j = j + 1

Next i
End Sub
 
Upvote 0
couple quick things i see...

try changing

Code: 
If Cells(i, 2) = "Today()" Then

to

Code: 
if cells(i,2).formula = "=TODAY()" then

also change

Code: 
Range("Fi:Si").Select

to

Code: 
Range("F" & i & ":S" & i).Select

also change

Code: 
Range("Bj").Select

to

Code: 
Range("B" & j).Select

NOTE: most of the time you don't need to select...you could change
Code: 
Range("F" & i & ":S" & i).Select
Selection.Copy

to

Code: 
Range("F" & i & ":S" & i).Copy


plus if you are always pasting to the last row on your scorecard sheet you may want to use somehting similar to this to find out the row rather than always starting at row 2 each time it's run...depends on what you are trying to do...

Code: 
lRow = Range("B" & Rows.Count).End(xlUp).Row
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Solved
Looping thru Checkboxes in a worksheet
2 3
Replies
21
Views
480
Domenic
D
D
  • Solved
Macro that won't run Trying to use DoWhile Loop and Offset to compare cells
Replies
8
Views
235
Micron
M
P
  • Question
Code used for comparison between 2 list.
Replies
3
Views
346
Tupe77
T
KlausW
  • Solved
Change destination between 2 sheets
Replies
2
Views
181
KlausW
KlausW
B
  • Solved
Do Until loop nested in For loop, How advance to the "Next x" in the For Loop
Replies
4
Views
301
CephasOz
CephasOz

Forum statistics

Threads
1,215,676
Messages
6,126,159
Members
449,295
Latest member
DSBerry

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top