Excel VBA: Refer Cell Value and Offset just specific Columns from another Sheet

[ShumsFaruk]

Good Day All Excel MVPs,

I know its sounds repeated subject, but I have been trying to get bits and pieces from different threads and post, but failing to get results as per my need. I am in a situation, where I am tired of searching further.

I need to print Invoice before that I would require Cells to be filled with a cell reference.

Cell reference is Worksheets("Invoice_Template").Range("K8").Value

Once VBA finds the cell reference value in Worksheets("WBEntryDetails") Column B then I would like to offset to Worksheets("Invoice_Template")

Offset range should be:
Worksheets("Invoice_Template").Range("D24").value = Worksheets("WBEntryDetails") Column G ( cell reference value would possibly be repeated few times, so we need to loop), but I would need to leave 3 rows for next i which means next offset should start .Range("D28")
Worksheets("Invoice_Template").Range("F24").value = Worksheets("WBEntryDetails") Column H ( cell reference value would possibly be repeated few times, so we need to loop), but I would need to leave 3 rows for next i which means next offset should start .Range("F28")
Worksheets("Invoice_Template").Range("D25").value = Worksheets("WBEntryDetails") Column I ( cell reference value would possibly be repeated few times, so we need to loop), but I would need to leave 3 rows for next i which means next offset should start .Range("D29")
Worksheets("Invoice_Template").Range("F25").value = Worksheets("WBEntryDetails") Column J ( cell reference value would possibly be repeated few times, so we need to loop), but I would need to leave 3 rows for next i which means next offset should start .Range("F29")
Worksheets("Invoice_Template").Range("H26").value = Worksheets("WBEntryDetails") Column M ( cell reference value would possibly be repeated few times, so we need to loop), but I would need to leave 3 rows for next i which means next offset should start .Range("H30")
Worksheets("Invoice_Template").Range("I24").value = Worksheets("WBEntryDetails") Column T ( cell reference value would possibly be repeated few times, so we need to loop), but I would need to leave 3 rows for next i which means next offset should start .Range("I28")

I tried to get one offset for Range("D24") as per below code, but I am failing to get desired result.

Option Explicit
Sub Update_InvoiceTemplate()
Dim ws As Worksheet, ws1 As Worksheet
Dim row As Integer
Dim LR As Long
Set ws = Worksheets("WBEntryDetails")
Set ws1 = Worksheets("Invoice_Template")
LR = WorksheetFunction.Max(24, ws1.Range("D" & Rows.Count).End(xlUp).row + 1)
row = 3

Do While (ws.Range("B" & row).Value <> "")

    If ws.Range("B" & row).Value = ws1.Range("K8").Value Then
        ws1.Cells(LR, row).Value = ws.Range("G" & row).Value
        
    End If

row = row + 1

Loop

End Sub

Please help.

 

[RCBricker]

I want to make certain that I understand this correctly.

You have an invoice template that you need to print based on data from other sheets. Based on the description it sounds as thought rows 1 - 23 are part of the invoice header and you are starting your itemization from rows 24 on downward.

questions on the layout.

what info is represented by column D? Column F? Column H? Column I?

now on to the moving of data.

is this a data table? if so what are the headers for G,H,I,J,M,& T?

how is it that you pair up the data from those columns to the invoice template?

Worksheets("Invoice_Template").Range("D24").value = Worksheets("WBEntryDetails") Column G

What cell in G? does this logic follow for the other cell pairings?

Why would the cell reference possibly repeat of few times?

Thanks

Rich

 

[ShumsFaruk]

Thanks RCBricker,

Yes you are right, rows 1 - 23 are part of the invoice header.

1. There are headers in previous cell of column D? Column F? Column H? Column I? These cells needs to offset the data from Worksheets("WBEntryDetails").

2. Yes, its a data table called InvoiceTable. Headers for Column G(Start Date), H(Start Time), I(End Date), J(End Time), M(Rate) & T(Activity)

3. I am trying to look for Invoice Number (Worksheets("Invoice_Template").Range("K8").Value) which are in Worksheets("WBEntryDetails").Column B(PS_Invoice_Number).

4. Whenever our vessels are ON/OFF hire, we record their activity on daily basis, some are on monthly contracts and some on short term contracts.

If charter is straight one month then we would extract just one line, if there are few ON & OFF Activity then these data would be recording all activities. We Invoice our client on monthly basis, which includes that particular vessel's activities for that month, that's why these invoice number would be repeated as per activities.

Hope I explain briefly.

Thanks again for helping.

 

[RCBricker]

ok hopefully the last bit of questions

So the code would search through column B and match an invoice number found in cell K8 of the invoice template and extract the data found in the invoicetable headers and return them to the invoice template. I assume the invoice then tallies up the total time times (*) the rate at the invoice footer.

also, columns D,F,H, & I each have headers that match the headers in the invoicetable,correct?

last question.

What row does invoicetable start on? That would include the header row.

thanks,

rich

 

[ShumsFaruk]

Yes Rich, the code would search through Column B (PS_Invoice_Number) and match an invoice number found in cell K8.

No, Columns D, F, H & I doesn't have headers, these are just cells adjacent to previous cell which are same like headers in InvoiceTable.

InvoiceTable data starts from row 2 column 2. row 1 is the headers.

Regards,
Shums

 

[RCBricker]

Yes Rich, the code would search through Column B (PS_Invoice_Number) and match an invoice number found in cell K8.

No, Columns D, F, H & I doesn't have headers, these are just cells adjacent to previous cell which are same like headers in InvoiceTable.

InvoiceTable data starts from row 2 column 2. row 1 is the headers.

Regards,
Shums

so there is nothing in column A for Worksheets("WBEntryDetails")?

I am not sure I understand what the following means:

"...these are just cells adjacent to previous cell which are same like headers in InvoiceTable."

what is the same like headers?

adjacent as in which direction?

rich

 

[ShumsFaruk]

Hi Rich,

There is Sr.No. in column A for Worksheets("WBEntryDetails")

Adjacent cells means, as lookup gonna show from D24 so range C24 is Start Time, same as header in InvoiceTable Header Column G(Start Date) and is goes on with other cell's results.

 

[ShumsFaruk]

Hi Rich,

I was trying to attach the sample file for better understanding, but seems there's no option.

 

[RCBricker]

Ok so c24 and c25 have start date and end date as values, correct?

Rich

 

[ShumsFaruk]

C24 has Start Date, C25 is End Date & C26 is Total Days and C27 would be blank row, that's why I specified in my initial post, I would like to offset first search result on D24 & second search on D28.