| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Need a little help with loop(ing)!
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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 - |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Need a little help with loop(ing)!
|
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Naming Worksheets - Loop within a loop issue | Excel Programming | |||
| Naming Worksheets - Loop within a loop issue | Excel Programming | |||
| (Complex) Loop within loop to create worksheets | Excel Programming | |||
| Advancing outer Loop Based on criteria of inner loop | Excel Programming | |||
| Problem adding charts using Do-Loop Until loop | Excel Programming | |||
Hybrid Mode
