Formula toLoop through range to add values to adjacent cells

VBABasix

Board Regular
Joined
Aug 15, 2005
Messages
52
I have code that is intended to loop through a range and add text in the adjacent field if a file is found. Everything works excpet that the text does not get put in the field. Any help identifying where the issues are would be helpful. Here is the code:
Sub test2()
Dim ii As Long
Dim Rng As String
Rng = ActiveCell.Value
Dim fln As String
Dim Rng1 As String
Dim ERng As String
Dim CRng As Integer
Dim SCell As Range
fln = ActiveCell.Offset(, ColumnOffset:=-1).Value
Rng1 = ActiveCell.Address
'MsgBox Right(Rng1, Len(Rng1) - 3)
Selection.End(xlDown).Select
ERng = ActiveCell.Address
'MsgBox Right(ERng, Len(ERng) - 3)
CRng = Right(ERng, Len(ERng) - 3) - Right(Rng1, Len(Rng1) - 3)
'MsgBox CRng
'Selection.End(xlUp).Select
'ActiveCell.Offset(RowOffset:=1).Activate
Application.Goto Reference:=Worksheets("Qry_Cust_Notification").Range(Rng1)
'Set ActiveSheet.ActiveCell = SCell
If CRng > 0 Then
For cc = 1 To CRng
With Application.FileSearch
.NewSearch
.Filename = Rng & "_20070101.pdf"
.SearchSubFolders = True
.LookIn = "N:\Annual Price Increase_2007\PDF Price List\WebPriceList\"
MsgBox "Just Before IF to Check for files"
If .Execute() > 0 Then
MsgBox .Execute
For ii = 1 To .FoundFiles.Count
MsgBox "In the Found Files Count"
ActiveCell.Offset(, ColumnOffset:=1).Activate
ActiveCell.Value = .FoundFiles(ii)
ActiveCell.Offset(RowOffset:=1, ColumnOffset:=-1).Activate

Next ii
Else
MsgBox "No File Found"
ActiveCell.Offset(RowOffset:=1).Activate
End If
End With
Next cc
Else
MsgBox "No Records Left"
End If
End Sub
 

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,300
Office Version
  1. 365
Platform
  1. Windows
I would suggest that you step through the code using F8.

You should check for various things - what the ActiveCell is and whether any files are actually being found.
 

VBABasix

Board Regular
Joined
Aug 15, 2005
Messages
52
I have stepped through this, the active cells are being captured as are the files. I think that the issue may be in the syntax of the loops.
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,300
Office Version
  1. 365
Platform
  1. Windows
I can't see anything wrong with the syntax of the loops, or any of the other code.

You say you've stepped through it?

Did you check back on the worksheet to see if anything was happening?
 

VBABasix

Board Regular
Joined
Aug 15, 2005
Messages
52
When I run the Macro it checks the firs record and that record does not have a matching file, it then moves to the next record and this does have a file, it seems to skip down into the section where it identifies no file found and then moves on as expected.
 

Forum statistics

Threads
1,136,507
Messages
5,676,263
Members
419,616
Latest member
quickflip

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
Top