Need a little help with loop(ing)!

  • Thread starter Thread starter Wopper
  • Start date Start date
W

Wopper

Hi all,

I would like to know how I can loop a code I wrote.

Every day I receive a file and the length of it changes. I would like
that the code I wrote works on every line in the document. I have no
clue how to do this (except that I think I need to use a loop
function).

Who can help me out here?
Thanks in advance.

Regards,

Wietse
 
Hi Wopper,

Try something like:

'==========>>
Public Sub Tester()
Dim WB As Workbook
Dim SH As Worksheet
Dim Rng As Range
Dim rCell As Range
Dim iLastRow As Long

Set WB = Workbooks("MyBook.xls") '<<==== CHANGE
Set SH = WB.Sheets("Sheet1") '<<==== CHANGE

With SH
iLastRow = LastRow(SH, .Columns("A:A"))
Set Rng = .Range("A1:A" & iLastRow)
End With

For Each rCell In Rng.Cells
With rCell
'do something, e.g:
If .Value > 10 Then
.Interior.ColorIndex = 6
Else
.Interior.ColorIndex = 5
End If
End With
Next rCell
End Sub

'--------------->
Function LastRow(SH As Worksheet, _
Optional Rng As Range)
If Rng Is Nothing Then
Set Rng = SH.Cells
End If

On Error Resume Next
LastRow = Rng.Find(What:="*", _
After:=Rng.Cells(1), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row
On Error GoTo 0
End Function
'<<==========
 
lr=cells(rows.count,"a").end(xlup).row
for i = 1 to lr
cells(i,"H")=1
next i
 
I think I'm doing something wrong so if one of you could help a
little.

This is the code I'm using and that needs to be looped! (so also line
2, 3 etc if they are there)
Also the output should not overwrite itself in the MEU sheet.

Please show me what to do.
Thanks

'Autofilter on cash that comes in
Sheets("Settled trades").Select
Rows("1:1").Select
Selection.AutoFilter
Selection.AutoFilter Field:=3, Criteria1:="=IN", Operator:=xlAnd

'Place the right values in the Booking CASH in (Settled) sheet for
MEU
Sheets("Settled trades").Select
Range("H2").Select
Selection.Copy
Sheets("Booking CASH in (Settled)").Select
Range("B4:B5,B8:B9").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Sheets("Settled trades").Select
Range("I2").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Booking CASH in (Settled)").Select
Range("J2:J9").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Sheets("Settled trades").Select
Range("D2").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Booking CASH in (Settled)").Select
Range("K2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Calculate
Sheets("Settled trades").Select
Range("B2").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Booking CASH in (Settled)").Select
Range("L6:M9").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Sheets("Settled trades").Select
Range("G2").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Booking CASH in (Settled)").Select
Range("L2:M5").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Range("N2").Select
ActiveCell.FormulaR1C1 = "=CONCATENATE(""OMR"",Settled trades!
RC[-13])"
Apllication.CutCopyMode = False
Selection.Copy
Range("N3:N9").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Range("O2").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "Part fails"
Range("O2").Select
Selection.Copy
Range("O3:O9").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Range("A2:X9").Select
Selection.Copy

'Place booking in MEU sheet
Windows("MEU.xls").Activate
Range("A3").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
 
You may send me your wb along with copies of these posts and EXACTLY what
you are trying to do.

--
Don Guillett
SalesAid Software
(e-mail address removed)
Wopper said:
I think I'm doing something wrong so if one of you could help a
little.

This is the code I'm using and that needs to be looped! (so also line
2, 3 etc if they are there)
Also the output should not overwrite itself in the MEU sheet.

Please show me what to do.
Thanks

'Autofilter on cash that comes in
Sheets("Settled trades").Select
Rows("1:1").Select
Selection.AutoFilter
Selection.AutoFilter Field:=3, Criteria1:="=IN", Operator:=xlAnd

'Place the right values in the Booking CASH in (Settled) sheet for
MEU
Sheets("Settled trades").Select
Range("H2").Select
Selection.Copy
Sheets("Booking CASH in (Settled)").Select
Range("B4:B5,B8:B9").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Sheets("Settled trades").Select
Range("I2").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Booking CASH in (Settled)").Select
Range("J2:J9").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Sheets("Settled trades").Select
Range("D2").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Booking CASH in (Settled)").Select
Range("K2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Calculate
Sheets("Settled trades").Select
Range("B2").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Booking CASH in (Settled)").Select
Range("L6:M9").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Sheets("Settled trades").Select
Range("G2").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Booking CASH in (Settled)").Select
Range("L2:M5").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Range("N2").Select
ActiveCell.FormulaR1C1 = "=CONCATENATE(""OMR"",Settled trades!
RC[-13])"
Apllication.CutCopyMode = False
Selection.Copy
Range("N3:N9").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Range("O2").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "Part fails"
Range("O2").Select
Selection.Copy
Range("O3:O9").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Range("A2:X9").Select
Selection.Copy

'Place booking in MEU sheet
Windows("MEU.xls").Activate
Range("A3").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False






lr=cells(rows.count,"a").end(xlup).row
for i = 1 to lr
cells(i,"H")=1
next i

--
Don Guillett
SalesAid Software
in message








- Show quoted text -
Click to expand...
Click to expand...
 
You must log in or register to reply here.

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

loop help 6
little help 16
Problem with a loop and column references 27
Looping through checkboxes 5
Loop returns only one value, does not loop & an assignment to columnhow-to problem. 26
Need to flag the lead ID of any contiguous ID's from a sorted list of ID's. 5
brain fart, need help 2
Looping 3

Back
Top