ExcelBanter - Need a little help with loop(ing)!

ExcelBanter (https://www.excelbanter.com/)

- Excel Programming (https://www.excelbanter.com/excel-programming/)

- - Need a little help with loop(ing)! (https://www.excelbanter.com/excel-programming/390621-need-little-help-loop-ing.html)

Need a little help with loop(ing)!

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

Need a little help with loop(ing)!

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
'<<==========

--
---
Regards,
Norman
Microsoft Excel MVP
"Wopper" wrote in message
oups.com...
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

Need a little help with loop(ing)!

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

On Jun 4, 2:30 pm, "Don Guillett" wrote:
lr=cells(rows.count,"a").end(xlup).row
for i = 1 to lr
cells(i,"H")=1
next i

--
Don Guillett
SalesAid Software
"Wopper" wrote in message

oups.com...

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- Hide quoted text -

- Show quoted text -

Need a little help with loop(ing)!