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

ShumsFaruk

Board Regular
Joined
Jul 24, 2009
Messages
93
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.

Code:
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.
 

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes

RCBricker

Well-known Member
Joined
Feb 4, 2003
Messages
1,560
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
 
Last edited:

ShumsFaruk

Board Regular
Joined
Jul 24, 2009
Messages
93
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

Well-known Member
Joined
Feb 4, 2003
Messages
1,560
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

Board Regular
Joined
Jul 24, 2009
Messages
93

ADVERTISEMENT

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

Well-known Member
Joined
Feb 4, 2003
Messages
1,560
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

Board Regular
Joined
Jul 24, 2009
Messages
93

ADVERTISEMENT

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

Board Regular
Joined
Jul 24, 2009
Messages
93
Hi Rich,

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

RCBricker

Well-known Member
Joined
Feb 4, 2003
Messages
1,560
Ok so c24 and c25 have start date and end date as values, correct?

Rich
 

ShumsFaruk

Board Regular
Joined
Jul 24, 2009
Messages
93
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.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,151,984
Messages
5,767,440
Members
425,414
Latest member
chwein

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