CODEWITHSUNDEEP
excelvba
last10seconds
last10seconds

Reputation: 333

Macro not copying rows correctly.

The purpose of this macro is to copy certain information from a log and create a purchase order from it. The code itself works, however I can't get it to copy the right information. Each log can have many different lines of data, so the code sifts through that and finds certain keywords and then is supposed to take information from that column, but could be 1-11 rows away. That is where I am running into a problem.

I need this to find the keyword and then depending on what information needs extracted go over x number of rows and take that number and put it into the PO at a certain spot. I am currently using xlToRight, but i just can't seem to figure out how to use it properly.

You can see that each section is laid out, I know that there is probably a better way to write the code out, but I am not too concerned about that.

Any help would be greatly appreciated! Thanks!

Sub Extract_job_info()

Dim Title As String
Dim Param As String
Dim Message As String
Dim defaultRef As String
Dim Sht As Worksheet, shtJob As Worksheet
Dim POSheet As Worksheet
Dim CreatePO As Integer
Dim InRowB As Long
Dim InColB As Range

Set POSheet = Sheets("Request for PO Template")

'set message details
Title = "Job Number"
Message = "Please enter the job number you would like to extract information from."
defaultRef = "Enter job number here"

Param = InputBox(Message, Title, defaultRef)

'find sheet
For Each Sht In ThisWorkbook.Worksheets
    If UCase(Sht.Name) = UCase(Param) Then
        Set shtJob = Sht
        Exit For
    End If
Next Sht

'If job does not exist
If shtJob Is Nothing Then
    MsgBox ("Sheet for '" & Param & "' was not found !")
Else
    If MsgBox("Would you like to extract job number '" & _
       Param & "' to make a PO?", vbYesNo, "Confirm") = vbYes Then

        InRowB = 2 'for testing


        'search for travel hours total
        Set InColB = Sht.Columns(InRowB).Find(What:="Cost", _
        LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByColumns, _
        SearchDirection:=xlNext, MatchCase:=False)

        If Not InColB Is Nothing Then

            POSheet.Range("F30").Value = InColB.End(xlToRight).Value

        Else
            MsgBox "'Cost' cell not found!", vbCriticalInColB.End(xlToRight).Value
        End If 'found "travel hours total"
        'search for travel hours amount
        Set InColB = Sht.Columns(InRowB).Find(What:="Grand Total", _
        LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByColumns, _
        SearchDirection:=xlNext, MatchCase:=False)

        If Not InColB Is Nothing Then
            POSheet.Range("B30").Value = InColB.End(xlToRight).Value
        Else
            MsgBox "'Cost' cell not found!", vbCritical
        End If 'found "travel hours amount"




        'search for Regular Hours total
        Set InColB = Sht.Columns(InRowB).Find(What:="Cost", _
        LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByColumns, _
        SearchDirection:=xlNext, MatchCase:=False)

        If Not InColB Is Nothing Then
            POSheet.Range("F31").Value = InColB.End(xlToRight).Value
        Else
            MsgBox "'Cost' cell not found!", vbCritical
        End If 'found "regular hours total"
        'search for regular hours total
        Set InColB = Sht.Columns(InRowB).Find(What:="Grand Total", _
        LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByColumns, _
        SearchDirection:=xlNext, MatchCase:=False)

        If Not InColB Is Nothing Then
            POSheet.Range("B31").Value = InColB.End(xlToRight).Value
        Else
            MsgBox "'Cost' cell not found!", vbCritical
        End If 'found "regular hours amount"





        'search for OT hours total
        Set InColB = Sht.Columns(InRowB).Find(What:="Cost", _
        LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByColumns, _
        SearchDirection:=xlNext, MatchCase:=False)

        If Not InColB Is Nothing Then
            POSheet.Range("F32").Value = InColB.End(xlToRight).Value
        Else
            MsgBox "'Cost' cell not found!", vbCritical
        End If 'found "OT total"
        'search for OT hours amount
        Set InColB = Sht.Columns(InRowB).Find(What:="Grand Total", _
        LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByColumns, _
        SearchDirection:=xlNext, MatchCase:=False)

        If Not InColB Is Nothing Then
            POSheet.Range("B32").Value = InColB.End(xlToRight).Value
        Else
            MsgBox "'Cost' cell not found!", vbCritical
        End If 'found "OT amount"



        'search for Engineering hours total
        Set InColB = Sht.Columns(InRowB).Find(What:="Cost", _
        LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByColumns, _
        SearchDirection:=xlNext, MatchCase:=False)

        If Not InColB Is Nothing Then
            POSheet.Range("F33").Value = InColB.End(xlToRight).Value
        Else
            MsgBox "'Cost' cell not found!", vbCritical
        End If 'found "Engineering hours total"
        'search for Engineering hours amount
        Set InColB = Sht.Columns(InRowB).Find(What:="Grand Total", _
        LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByColumns, _
        SearchDirection:=xlNext, MatchCase:=False)

        If Not InColB Is Nothing Then
            POSheet.Range("B33").Value = InColB.End(xlToRight).Value
        Else
            MsgBox "'Cost' cell not found!", vbCritical
        End If 'found "Engineering amount"




        'search for Engineering OT total
        Set InColB = Sht.Columns(InRowB).Find(What:="Cost", _
        LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByColumns, _
        SearchDirection:=xlNext, MatchCase:=False)

        If Not InColB Is Nothing Then
            POSheet.Range("F34").Value = InColB.End(xlToRight).Value
        Else
            MsgBox "'Cost' cell not found!", vbCritical
        End If 'found "Engineering OT total"
        'search for Engineering OT amount
        Set InColB = Sht.Columns(InRowB).Find(What:="Grand Total", _
        LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByColumns, _
        SearchDirection:=xlNext, MatchCase:=False)

        If Not InColB Is Nothing Then
            POSheet.Range("B34").Value = InColB.End(xlToRight).Value
        Else
            MsgBox "'Cost' cell not found!", vbCritical
        End If 'found "Engineering OT amount"



        'search for Milage total
        Set InColB = Sht.Columns(InRowB).Find(What:="Cost", _
        LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByColumns, _
        SearchDirection:=xlNext, MatchCase:=False)

        If Not InColB Is Nothing Then
            POSheet.Range("F36").Value = InColB.End(xlToRight).Value
        Else
            MsgBox "'Cost' cell not found!", vbCritical
        End If 'found "Milage total"
        'search for milage amount
        Set InColB = Sht.Columns(InRowB).Find(What:="Grand Total", _
        LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByColumns, _
        SearchDirection:=xlNext, MatchCase:=False)

        If Not InColB Is Nothing Then
            POSheet.Range("B34").Value = InColB.End(xlToRight).Value
        Else
            MsgBox "'Cost' cell not found!", vbCritical
        End If 'found "milage amount"






        'search for Travel & Lodging  total
        Set InColB = Sht.Columns(InRowB).Find(What:="Cost", _
        LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByColumns, _
        SearchDirection:=xlNext, MatchCase:=False)

        If Not InColB Is Nothing Then
            POSheet.Range("F35").Value = InColB.End(xlToRight).Value
        Else
            MsgBox "'Cost' cell not found!", vbCritical
        End If 'found "Travel & Lodging hours total"
        'search for Travel & Lodging amount
        Set InColB = Sht.Columns(InRowB).Find(What:="Grand Total", _
        LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByColumns, _
        SearchDirection:=xlNext, MatchCase:=False)

        If Not InColB Is Nothing Then
            POSheet.Range("B35").Value = InColB.End(xlToRight).Value
        Else
            MsgBox "'Cost' cell not found!", vbCritical
        End If 'found "Travel & Lodging amount"




        'search for Milage total
        Set InColB = Sht.Columns(InRowB).Find(What:="Cost", _
        LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByColumns, _
        SearchDirection:=xlNext, MatchCase:=False)

        If Not InColB Is Nothing Then
            POSheet.Range("F36").Value = InColB.End(xlToRight).Value
        Else
            MsgBox "'Cost' cell not found!", vbCritical
        End If 'found "Milage total"
        'search for milage amount
        Set InColB = Sht.Columns(InRowB).Find(What:="Grand Total", _
        LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByColumns, _
        SearchDirection:=xlNext, MatchCase:=False)

        If Not InColB Is Nothing Then
            POSheet.Range("B37").Value = InColB.End(xlToRight).Value
        Else
            MsgBox "'Cost' cell not found!", vbCritical
        End If 'found "milage amount"




        'search for Parts total
        Set InColB = Sht.Columns(InRowB).Find(What:="Cost", _
        LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByColumns, _
        SearchDirection:=xlNext, MatchCase:=False)

        If Not InColB Is Nothing Then
            POSheet.Range("F38").Value = InColB.End(xlToRight).Value
        Else
            MsgBox "'Cost' cell not found!", vbCritical
        End If 'found "parts total"
        'search for parts amount
        Set InColB = Sht.Columns(InRowB).Find(What:="Grand Total", _
        LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByColumns, _
        SearchDirection:=xlNext, MatchCase:=False)

        If Not InColB Is Nothing Then
            POSheet.Range("B38").Value = InColB.End(xlToRight).Value
        Else
            MsgBox "'Cost' cell not found!", vbCritical
        End If 'found "milage amount"




        'search for Freight total
        Set InColB = Sht.Columns(InRowB).Find(What:="Cost", _
        LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByColumns, _
        SearchDirection:=xlNext, MatchCase:=False)

        If Not InColB Is Nothing Then
            POSheet.Range("F42").Value = InColB.End(xlToRight).Value
        Else
            MsgBox "'Cost' cell not found!", vbCritical
        End If 'found "Freight total"


     End If 'user confirmed extract

End If 'got sheet




End Sub

Upvotes: 0

Views: 28

Answers (1)

basodre
basodre

Reputation: 5770

Does the OFFSET method serve your need? OFFSET returns a Range object relative to another Range object using the syntax .OFFSET(rowOffset, columnOffset). For example, Range("A1").Offset(1,2) returns cell C2 (1 row below, and 2 columns to the right).

In your case, it seems that InColB is the Range that needs to be offset. Since you only need to Offset columns, the syntax would be something like InColB.Offset(,4)

Does this help?

Upvotes: 1

Related Questions