Results 1 to 2 of 2
  1. #1
    New Lounger
    Join Date
    Sep 2010
    Location
    Cheltenham, Gloucestershire, UK
    Posts
    12
    Thanks
    4
    Thanked 0 Times in 0 Posts
    I have a quite complex macro which is run once a week it downloads data from an FTP source, drags that data into a series of worksheets, adding them from the first empty row after of previous entries. Finally it then opens another workbook which it uses to run a series of Vlookups to poplulate cells in the newly entered rows.

    This works brilliantly except the very first cell of the first new row using the vlookup gets missed. Other cell entries in teh same row no problem, (ie cells before and cells after the first vlookup). Subsequent rows no problem.

    'Update Site Breakdown
    'Get last row
    LastRow = myWorkbook.Sheets(updSheetName & " Breakdown").Range("A65536").End(xlUp).Row + 1
    'Find Cell for w/c
    'Find cell for Item
    Set rng2 = myWorkbook.Sheets(updSheetName & " Breakdown").Range("A:A")

    Set rFoundCell = rng2.Find(What:=weDate, LookIn:=xlFormulas, LookAt:=xlPart, SearchDirection:=xlNext)
    If rFoundCell Is Nothing Then
    myWorkbook.Sheets(updSheetName & " Breakdown").Cells(LastRow, 1) = weDate
    myWorkbook.Sheets(updSheetName & " Breakdown").Cells(LastRow, 2) = elements(3)
    myWorkbook.Sheets(updSheetName & " Breakdown").Cells(LastRow, 13).Formula = "=SUM(C" & LastRow & ":I" & LastRow & ")"
    myWorkbook.Sheets(updSheetName & " Breakdown").Cells(LastRow, 23).Formula = "=SUM(P" & LastRow & ":V" & LastRow & ")"
    myWorkbook.Sheets(updSheetName & " Breakdown").Cells(LastRow, 3).Formula = "=VLOOKUP(B" & LastRow & ",'[Codes.xlsx]Sheet2'!$C:$E6,2,FALSE)"
    myWorkbook.Sheets(updSheetName & " Breakdown").Cells(LastRow, 4).Formula = "=VLOOKUP(B" & LastRow & ",'[Codes.xlsx]Sheet2'!$C:$F,3,FALSE)"
    myWorkbook.Sheets(updSheetName & " Breakdown").Cells(LastRow, 5).Formula = "=VLOOKUP(B" & LastRow & ",'[Codes.xlsx]Sheet2'!$C:$G,4,FALSE)"
    myWorkbook.Sheets(updSheetName & " Breakdown").Cells(LastRow, 14).Formula = "=VLOOKUP(B" & LastRow & ",'[Codes.xlsx]Sheet2'!$C:$H,5,FALSE)"
    If elements(5) = "150" Then
    myWorkbook.Sheets(updSheetName & " Breakdown").Cells(LastRow, 5 + Weekday(Left(elements(2), 4) & "-" & Mid(elements(2), 5, 2) & "-" & Right(elements(2), 2))) = myWorkbook.Sheets(updSheetName & " Breakdown").Cells(LastRow, 5 + Weekday(Left(elements(2), 4) & "-" & Mid(elements(2), 5, 2) & "-" & Right(elements(2), 2))) + (Left(elements(4), 5) & "." & Right(elements(4), 2) * elements(6))
    myWorkbook.Sheets(updSheetName & " Breakdown").Cells(LastRow, 15 + Weekday(Left(elements(2), 4) & "-" & Mid(elements(2), 5, 2) & "-" & Right(elements(2), 2))) = myWorkbook.Sheets(updSheetName & " Breakdown").Cells(LastRow, 15 + Weekday(Left(elements(2), 4) & "-" & Mid(elements(2), 5, 2) & "-" & Right(elements(2), 2))) + 1
    Else
    myWorkbook.Sheets(updSheetName & " Breakdown").Cells(LastRow, 5 + Weekday(Left(elements(2), 4) & "-" & Mid(elements(2), 5, 2) & "-" & Right(elements(2), 2))) = myWorkbook.Sheets(updSheetName & " Breakdown").Cells(LastRow, 5 + Weekday(Left(elements(2), 4) & "-" & Mid(elements(2), 5, 2) & "-" & Right(elements(2), 2))) - (Left(elements(4), 5) & "." & Right(elements(4), 2) * elements(6))
    myWorkbook.Sheets(updSheetName & " Breakdown").Cells(LastRow, 15 + Weekday(Left(elements(2), 4) & "-" & Mid(elements(2), 5, 2) & "-" & Right(elements(2), 2))) = myWorkbook.Sheets(updSheetName & " Breakdown").Cells(LastRow, 15 + Weekday(Left(elements(2), 4) & "-" & Mid(elements(2), 5, 2) & "-" & Right(elements(2), 2))) - 1
    End If
    LastRow = LastRow + 1
    Else
    updRow = 0
    For j = rFoundCell.Row To LastRow
    If myWorkbook.Sheets(updSheetName & " Breakdown").Cells(j, 1) = weDate Then
    If myWorkbook.Sheets(updSheetName & " Breakdown").Cells(j, 2) = elements(3) Then
    updRow = j
    Exit For
    End If
    Else
    Exit For
    End If
    Next
    If updRow = 0 Then
    'Create barcode under date
    rFoundCell.EntireRow(rFoundCell.Count + 1).Insert
    rFoundCell.Cells(rFoundCell.Count + 1, 13).Formula = "=SUM(F" & rFoundCell.Row + 1 & ":L" & rFoundCell.Row + 1 & ")"
    rFoundCell.Cells(rFoundCell.Count + 1, 23).Formula = "=SUM(P" & rFoundCell.Row + 1 & ":V" & rFoundCell.Row + 1 & ")"
    rFoundCell.Cells(rFoundCell.Count + 1, 3).Formula = "=VLOOKUP(B" & rFoundCell.Row + 1 & ",'[Codes.xlsx]Sheet2'!$C:$E,2,FALSE)"
    rFoundCell.Cells(rFoundCell.Count + 1, 4).Formula = "=VLOOKUP(B" & rFoundCell.Row + 1 & ",'[Codes.xlsx]Sheet2'!$C:$F,3,FALSE)"
    rFoundCell.Cells(rFoundCell.Count + 1, 5).Formula = "=VLOOKUP(B" & rFoundCell.Row + 1 & ",'[Codes.xlsx]Sheet2'!$C:$G,4,FALSE)"
    rFoundCell.Cells(rFoundCell.Count + 1, 14).Formula = "=VLOOKUP(B" & rFoundCell.Row + 1 & ",'[Codes.xlsx]Sheet2'!$C:$H,5,FALSE)"

    rFoundCell.Cells(rFoundCell.Count + 1, 1) = weDate
    rFoundCell.Cells(rFoundCell.Count + 1, 2) = elements(3)
    If elements(5) = "150" Then
    rFoundCell.Cells(rFoundCell.Count + 1, 5 + Weekday(Left(elements(2), 4) & "-" & Mid(elements(2), 5, 2) & "-" & Right(elements(2), 2))) = rFoundCell.Cells(rFoundCell.Count + 1, 5 + Weekday(Left(elements(2), 4) & "-" & Mid(elements(2), 5, 2) & "-" & Right(elements(2), 2))) + (Left(elements(4), 5) & "." & Right(elements(4), 2) * elements(6))
    rFoundCell.Cells(rFoundCell.Count + 1, 15 + Weekday(Left(elements(2), 4) & "-" & Mid(elements(2), 5, 2) & "-" & Right(elements(2), 2))) = rFoundCell.Cells(rFoundCell.Count + 1, 15 + Weekday(Left(elements(2), 4) & "-" & Mid(elements(2), 5, 2) & "-" & Right(elements(2), 2))) + 1
    Else
    rFoundCell.Cells(rFoundCell.Count + 1, 5 + Weekday(Left(elements(2), 4) & "-" & Mid(elements(2), 5, 2) & "-" & Right(elements(2), 2))) = rFoundCell.Cells(rFoundCell.Count + 1, 5 + Weekday(Left(elements(2), 4) & "-" & Mid(elements(2), 5, 2) & "-" & Right(elements(2), 2))) - (Left(elements(4), 5) & "." & Right(elements(4), 2) * elements(6))
    rFoundCell.Cells(rFoundCell.Count + 1, 15 + Weekday(Left(elements(2), 4) & "-" & Mid(elements(2), 5, 2) & "-" & Right(elements(2), 2))) = rFoundCell.Cells(rFoundCell.Count + 1, 15 + Weekday(Left(elements(2), 4) & "-" & Mid(elements(2), 5, 2) & "-" & Right(elements(2), 2))) - 1
    End If
    Else
    Else
    updRow = 0
    For j = rFoundCell.Row To LastRow
    If myWorkbook.Sheets(updSheetName & " Breakdown").Cells(j, 1) = weDate Then
    If myWorkbook.Sheets(updSheetName & " Breakdown").Cells(j, 2) = elements(3) Then
    updRow = j
    Exit For
    End If
    Else
    Exit For
    End If
    Next
    If updRow = 0 Then
    'Create barcode under date
    'this is the second lot of Vlookups to be updated if you change the file name - it does not matter if you
    'have moved the file to another folder as long as you have updated the linkfile reference at the beginning of
    'this script - remember is .xlsx - Roberta
    rFoundCell.EntireRow(rFoundCell.Count + 1).Insert
    rFoundCell.Cells(rFoundCell.Count + 1, 13).Formula = "=SUM(F" & rFoundCell.Row + 1 & ":L" & rFoundCell.Row + 1 & ")"
    rFoundCell.Cells(rFoundCell.Count + 1, 23).Formula = "=SUM(P" & rFoundCell.Row + 1 & ":V" & rFoundCell.Row + 1 & ")"
    rFoundCell.Cells(rFoundCell.Count + 1, 3).Formula = "=VLOOKUP(B" & rFoundCell.Row + 1 & ",'[Codes.xlsx]Sheet2'!$C:$E,2,FALSE)"
    rFoundCell.Cells(rFoundCell.Count + 1, 4).Formula = "=VLOOKUP(B" & rFoundCell.Row + 1 & ",'[Codes.xlsx]Sheet2'!$C:$F,3,FALSE)"
    rFoundCell.Cells(rFoundCell.Count + 1, 5).Formula = "=VLOOKUP(B" & rFoundCell.Row + 1 & ",'[Codes.xlsx]Sheet2'!$C:$G,4,FALSE)"
    rFoundCell.Cells(rFoundCell.Count + 1, 14).Formula = "=VLOOKUP(B" & rFoundCell.Row + 1 & ",'[Codes.xlsx]Sheet2'!$C:$H,5,FALSE)"


    rFoundCell.Cells(rFoundCell.Count + 1, 1) = weDate
    rFoundCell.Cells(rFoundCell.Count + 1, 2) = elements(3)
    If elements(5) = "150" Then
    rFoundCell.Cells(rFoundCell.Count + 1, 5 + Weekday(Left(elements(2), 4) & "-" & Mid(elements(2), 5, 2) & "-" & Right(elements(2), 2))) = rFoundCell.Cells(rFoundCell.Count + 1, 5 + Weekday(Left(elements(2), 4) & "-" & Mid(elements(2), 5, 2) & "-" & Right(elements(2), 2))) + (Left(elements(4), 5) & "." & Right(elements(4), 2) * elements(6))
    rFoundCell.Cells(rFoundCell.Count + 1, 15 + Weekday(Left(elements(2), 4) & "-" & Mid(elements(2), 5, 2) & "-" & Right(elements(2), 2))) = rFoundCell.Cells(rFoundCell.Count + 1, 15 + Weekday(Left(elements(2), 4) & "-" & Mid(elements(2), 5, 2) & "-" & Right(elements(2), 2))) + 1
    Else
    rFoundCell.Cells(rFoundCell.Count + 1, 5 + Weekday(Left(elements(2), 4) & "-" & Mid(elements(2), 5, 2) & "-" & Right(elements(2), 2))) = rFoundCell.Cells(rFoundCell.Count + 1, 5 + Weekday(Left(elements(2), 4) & "-" & Mid(elements(2), 5, 2) & "-" & Right(elements(2), 2))) - (Left(elements(4), 5) & "." & Right(elements(4), 2) * elements(6))
    rFoundCell.Cells(rFoundCell.Count + 1, 15 + Weekday(Left(elements(2), 4) & "-" & Mid(elements(2), 5, 2) & "-" & Right(elements(2), 2))) = rFoundCell.Cells(rFoundCell.Count + 1, 15 + Weekday(Left(elements(2), 4) & "-" & Mid(elements(2), 5, 2) & "-" & Right(elements(2), 2))) - 1
    End If
    Else
    'Add to existing entry
    myWorkbook.Sheets(updSheetName & " Breakdown").Cells(updRow, 5 + Weekday(Left(elements(2), 4) & "-" & Mid(elements(2), 5, 2) & "-" & Right(elements(2), 2))) = myWorkbook.Sheets(updSheetName & " Breakdown").Cells(updRow, 5 + Weekday(Left(elements(2), 4) & "-" & Mid(elements(2), 5, 2) & "-" & Right(elements(2), 2))) + (Left(elements(4), 5) & "." & Right(elements(4), 2) * elements(6))
    myWorkbook.Sheets(updSheetName & " Breakdown").Cells(updRow, 15 + Weekday(Left(elements(2), 4) & "-" & Mid(elements(2), 5, 2) & "-" & Right(elements(2), 2))) = myWorkbook.Sheets(updSheetName & " Breakdown").Cells(updRow, 15 + Weekday(Left(elements(2), 4) & "-" & Mid(elements(2), 5, 2) & "-" & Right(elements(2), 2))) + 1
    End If
    End If
    Next

    brk:
    Resume Next
    End Sub
    'Add to existing entry
    myWorkbook.Sheets(updSheetName & " Breakdown").Cells(updRow, 5 + Weekday(Left(elements(2), 4) & "-" & Mid(elements(2), 5, 2) & "-" & Right(elements(2), 2))) = myWorkbook.Sheets(updSheetName & " Breakdown").Cells(updRow, 5 + Weekday(Left(elements(2), 4) & "-" & Mid(elements(2), 5, 2) & "-" & Right(elements(2), 2))) + (Left(elements(4), 5) & "." & Right(elements(4), 2) * elements(6))
    myWorkbook.Sheets(updSheetName & " Breakdown").Cells(updRow, 15 + Weekday(Left(elements(2), 4) & "-" & Mid(elements(2), 5, 2) & "-" & Right(elements(2), 2))) = myWorkbook.Sheets(updSheetName & " Breakdown").Cells(updRow, 15 + Weekday(Left(elements(2), 4) & "-" & Mid(elements(2), 5, 2) & "-" & Right(elements(2), 2))) + 1
    End If
    End If
    Next

    brk:
    Resume Next
    End Sub


    Many thanks

    Roberta

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    Could you attach a sample workbook that the code runs on?

    It would be good to also include a sheet which demonstrates what you want the final output to look like from the example so we can see the desired vs the actual...

    Steve

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •