How To Recharge Loop Mobile?

Vbaerror in loop

• QUESTION: I have written some code in vba. It is suppose to format cells depending on the fact if in column R there is a cell that is equal to the file name: Dim RowNumber As Inteeger Dim nameOfFile As String RowNumber = 4800 nameOfFile = ExampleName Do While RowNumber > 1 If Range("R1:R1").Offset(RowNumber, 0) = nameOfFile Then Rows(RowNumber).Font.Bold = True Else Rows(RowNumber).Font.Italic = True End If RowNumber = RowNumber - 1 Loop This Code works fine without the Do While loop (for single rows, I just give the row number for example =20 or =30). But When I try to run it with the loop it just goes trough all the rows, and does not change anything (nor italic or bold). I've tried to run the loop from the beginning of the sheet (Do while rowNumber Do While RowNumber > 1 If Range("R1:R1").Offset(RowNumber, 0) = nameOfFile Then Rows(RowNumber + 1).Font.Bold = True Else Rows(RowNumber + 1).Font.Italic = True End If RowNumber = RowNumber - 1 Loop End Sub and it worked fine for me. I altered the line nameofFile = ExampleName to nameofFile = "ExampleName" and placed ExampleName in several cells in column R. also, Range("R1:R1").Offset(RowNumber, 0) and RowNumber do not refer to the same row. RowNumber will be 1 less than the row being checked. So I adjusted the code to reflect that. If you run that code, it should at least change all rows to Italics. At least rows between and including row 3 and row 4801 If you expect some rows to be bold and they are not, then you need to check what is the value of nameofFile. If you want to send a sample workbook that you think is not working, I can take a look. ---------- FOLLOW-UP ---------- QUESTION: Thank you for your help. I've checked, and the code works great for one file. But I have 400 files, and I wanted the macro to go trough it automatically. I've made a second loop, but altrough the macro works for some time, it doesn't do anything, not even for one file. Please find the code below: Sub TraceOrder() Dim excelFile As Variant Dim path As Variant Dim rowNumber As Integer Dim nameOfFile As String path = "C:\Documents and Settings\Kuba\Desktop\Copy of Z TRX\" excelFile = Dir(path & "*.xls") Do While excelFile <> "" Workbooks.Open Filename:=path & excelFile excelFile = Dir Workbooks(Workbooks.Count).Activate '######################################### This works fine for singe file nameOfFile = Left(ActiveWorkbook.Name, (Len(ActiveWorkbook.Name) - 4)) rowNumber = 0 Do While rowNumber ActiveWorkbook.Close saveChanges:=True Loop End Sub I'm afraid, I've made some mistake in the outer loop (the one that should process trough all the files in the Copy of Z TRX folder, but can't find it. Could you please help me? Best Kuba

• Answer:

  Kuba, I don't see any glaring error in your code and if it runs that would indicative that you don't have a syntax error. One question would be what sheet do you want to process or do the workbooks only have one worksheet? I just process the first sheet - but you could loop through sheets if you need to or specify a specific sheet. Sub TraceOrder() Dim excelFile As Variant Dim path As Variant Dim rowNumber As Long Dim nameOfFile As String Dim bk As Workbook, maxrow As Long Dim iloc As Long, sh As Worksheet path = "C:\Documents and Settings\Kuba\Desktop\Copy of Z TRX\" excelFile = Dir(path & "*.xls") Do While excelFile <> "" Set bk = Workbooks.Open(Filename:=path & excelFile) Set sh = bk.Worksheets(1) '######################################### This works fine for singe file ' assumes a book name will only have a single period in it iloc = InStr(1, bk.Name, ".", vbTextCompare) nameOfFile = Left(bk.Name, iloc - 1) rowNumber = 1 maxrow = sh.UsedRange.Rows.count sh.Rows.Font.Bold = False sh.Rows.Font.Underline = False For rowNumber = 1 To maxrow If InStr(1, Cells(rowNumber, "R"), nameOfFile, vbTextCompare) > 0 Then Rows(rowNumber).Font.Bold = True Else Rows(rowNumber).Font.Underline = True End If rowNumber = rowNumber + 1 Next '########################################## bk.Close saveChanges:=True excelFile = Dir Loop End Sub I don't have the environment to test it, but that is about as robust as I can think to make it. If you have data problems, there is no way I can see that.