VBA code selecting a range with a blank cell

[Skooks]

Hi all first time poster. Been getting some great tips and lessons from the board but i'm stuck on some VBA code.

I have pieced together and modified some code to do a task i'm after but one section i can't get to work.
I'm not good at writing VBA but i'm capable of modifying something to work. sometimes

i call this below macro from another macro and it works up until it comes across a blank cell in a column D of the spreadsheet

It essentially looks down the D column starting with D5 and if the cell has a number in it it then looks for a PDF file in one folder and if it exists moves it to another folder. Then moves onto the next cell. The issue is if ( for example) the first 5 cells have numbers in them it works flawlessly but if the 6th cell is blank and the 7th cell has numbers in it the file relating to the 7th cell doesnt get moved. I'm assuming i need to specify a better range but not sure how i do that. Im guessing but assuming the current code ( in code language) says " keep looping down the D column until it comes across a blank cell then stop" ... how do i change that to keep looping until D150 or similar ?

Sub Movefile()
Dim rng As Range
Dim oldPath As String
Dim newPath As String
Dim oldFile As String
Dim newFile As String
Dim Foldermonth As String

'================================================
'Set the paths to the folders you are processing
'REMEMBER END BACKSLASH
'================================================
oldPath = "C:\Users\larry\Documents\VBA test folder\Purchase order"
newPath = "C:\Users\larry\Documents\VBA test folder\Purchase order"
Foldermonth = ActiveSheet.Range("B2").Value

'=======================================================
'set up the start of the range you want to loop through
'EDIT SHEET NAME AND FIRST CELL ADDRESS IF NECESSARY
'======================================================
Set rng = ActiveSheet.Range("E5")

'==================================================
'loop through column B until you find an empty cell
'==================================================
Do Until rng = ""

'build up the full path to the old file
oldFile = oldPath & "" & rng.Value & ".pdf"

'build up the full path to the new file you want to create
newFile = newPath & "" & Foldermonth & "" & rng.Value & "" & rng.Value & ".pdf"

'copy the old file to the new new folder
If Len(Dir(oldFile, vbDirectory)) > 0 Then
FileCopy oldFile, newFile
Kill (oldFile)
End If
'get the next file by moving down one row
Set rng = rng.Offset(1, 0)
Loop
End Sub



Thanks in advance for any help

 

[Skooks]

OOOPS... madea mistake already. I should of said it looks down the E column not D

 

[GiorgiPepe]

you can find Last filled cell in E column by this code :

LastCell = cells(rows.count, "E").end(xlup)

and then set new range
Set rng = range(rng,lastcell)

then instead of using Do loop you should use For each loop like:

for each cell in rng

next cell

but do not forget to include in case Cell is empty what should code do

 

[Skooks]

Thanks, i'll go read up on how to do that so i can write the code to suit and see how i go. cheers

 

[Skooks]

i'm not having much success with my coding skills. Would anyone be so kind as to modify that section of my original code to show me how it should be written to :
Find the last cell in column E and then loop through that range... skipping the blank cells but move the files for cells that arn't blank

 

[GiorgiPepe]

Sub Movefile()
Dim rng As Range
Dim oldPath As String
Dim newPath As String
Dim oldFile As String
Dim newFile As String
Dim Foldermonth As String
Dim LastCell As Range
Dim LoopRng As Range


'================================================
'Set the paths to the folders you are processing
'REMEMBER END BACKSLASH
'================================================
oldPath = "C:\Users\larry\Documents\VBA test folder\Purchase order"
newPath = "C:\Users\larry\Documents\VBA test folder\Purchase order"
Foldermonth = ActiveSheet.Range("B2").Value


'=======================================================
'set up the start of the range you want to loop through
'EDIT SHEET NAME AND FIRST CELL ADDRESS IF NECESSARY
'======================================================
Set rng = ActiveSheet.Range("E5")
Set LastCell = ActiveSheet.Cells(Rows.Count, rng.Column).End(xlUp)
Set LoopRng = ActiveSheet.Range(rng, LastCell)


'==================================================
'loop through column B until you find an empty cell
'==================================================
For Each cell In LoopRng


If Not IsEmpty(cell) Then


'build up the full path to the old file
oldFile = oldPath & "" & rng.Value & ".pdf"


'build up the full path to the new file you want to create
newFile = newPath & "" & Foldermonth & "" & rng.Value & "" & rng.Value & ".pdf"


'copy the old file to the new new folder
If Len(Dir(oldFile, vbDirectory)) > 0 Then
FileCopy oldFile, newFile
Kill (oldFile)
End If
'get the next file by moving down one row
Set rng = rng.Offset(1, 0)


Else
'write code what should macro do in case of empty cell
End If
Next cell
End Sub


try this one, and it needs to add part when cell is empty, i mentioned comment in code for you

 

[Fluff]

Untested, but try

Sub Movefile()
Dim rng As Range
Dim oldPath As String
Dim newPath As String
Dim oldFile As String
Dim newFile As String
Dim Foldermonth As String
Dim LastCell As Range
Dim LoopRng As Range
[COLOR=#0000ff]Dim Cell As Range[/COLOR]

'================================================
'Set the paths to the folders you are processing
'REMEMBER END BACKSLASH
'================================================
oldPath = "C:\Users\larry\Documents\VBA test folder\Purchase order"
newPath = "C:\Users\larry\Documents\VBA test folder\Purchase order"
Foldermonth = ActiveSheet.Range("B2").Value


'=======================================================
'set up the start of the range you want to loop through
'EDIT SHEET NAME AND FIRST CELL ADDRESS IF NECESSARY
'======================================================
Set rng = ActiveSheet.Range("E5")
Set LastCell = ActiveSheet.Cells(Rows.Count, rng.column).End(xlUp)
Set LoopRng = ActiveSheet.Range(rng, LastCell)


'==================================================
'loop through column B until you find an empty cell
'==================================================
For Each Cell In LoopRng
   
   
   If Not IsEmpty(Cell) Then
   
      
      'build up the full path to the old file
      oldFile = oldPath & "\" & [COLOR=#0000ff]Cell[/COLOR].Value & ".pdf"
      
      
      'build up the full path to the new file you want to create
      newFile = newPath & "\" & Foldermonth & "\" & [COLOR=#0000ff]Cell[/COLOR].Value & "\" & [COLOR=#0000ff]Cell[/COLOR].Value & ".pdf"
      
      
      'copy the old file to the new new folder
      If Len(Dir(oldFile, vbDirectory)) > 0 Then
      FileCopy oldFile, newFile
      Kill (oldFile)
   End If
   
Next Cell
End Sub

 

[Skooks]

Thank you to both of you for the reply. Unfortunately i get a compile error on both codes.... " next without for" just before the end sub.

 

[GiorgiPepe]

you get error on the first cell or when it finds empty cell?

 

[Skooks]

It doesnt seem to matter. It's pretty well instant as none of the files are getting moved