Cell Referencing

[Ashali]

I have dates in row 3 starting column 4 (D). And column B8 onwards, I have names of departments. The list of departments can expand or shrink on a daily basis so it is not a static reference either. I need a macro which looks up cell for that particular day corresponding to the row that contains a certain value/department. I have today's date in cell "D1". So far my code looks like:

Please Login or Register  to view this content.

This code can get to todays date but the relevant department might not exist in ActiveCell.Offset(6, 0).Select
How do I refer moving to column B and offset by row till it gets to a certain value and then move back to the column of todays date?

[BuckoAk]

Can you upload a sample workbook

[Ashali]

I have enclosed a sample of the sheet that I am working on.

[BuckoAk]

How do I refer moving to column B and offset by row till it gets to a certain value and then move back to the column of todays date?

You could do something like, just an example....
Of Cell address and/or value of Cell without moving the curser

Please Login or Register  to view this content.

looks up cell for that particular day corresponding to the row that contains a certain value/department

What determines the Department? Value? is this value the Date in Cell-D2?

Just trying to get a better understanding on how this sheet works.

[Ashali]

Basically the values in column B are departments that save their files daily in a folder, for instance CPD IBC - IN refers to inflow from CPD IBC & CPD IBC - OUT refers to outflow for the same department. This macro will open these individual files and put a vlookup in the macro file to read receipts & payments values for that particular day against the relevant department name. So I need to get to the cell that corresponds to today's date for a particular department where the code will put a vlookup formula.

[BuckoAk]

Ashali,

Just a few more questions.
Does the Formula work on your end, the macro errors out and I think it's becouse I don't have access to the linked sheets as you do?
It does not like that formula at all.

Per the sheet you uploaded, as follows. example Date = 9/4/2012
For each of the Departments in Column F...F8, F9, F10, F11 thru F15 would have the following formula in the Cells if the macro was working correctly.

• "=VLOOKUP(A5,'[" & OPEN1 & "]" & "CPD IBR - IN'!C4:C6,3,0)"
• "=VLOOKUP(A5,'[" & OPEN1 & "]" & "CPD IBC - IN'!C4:C6,3,0)"
• "=VLOOKUP(A5,'[" & OPEN1 & "]" & "CPD CORP - IN'!C4:C6,3,0)"
• "=VLOOKUP(A5,'[" & OPEN1 & "]" & "CPD REMIT - IN'!C4:C6,3,0)"
• "=VLOOKUP(A5,'[" & OPEN1 & "]" & "TF AUH - IN'!C4:C6,3,0)"
• "=VLOOKUP(A5,'[" & OPEN1 & "]" & "TF DXB - IN'!C4:C6,3,0)"
• "=VLOOKUP(A5,'[" & OPEN1 & "]" & "BROKERAGE - IN'!C4:C6,3,0)"

And so on for the " - OUT" side of things.

These question, I am trying to establish how to look for the departments...Array, Rows, Range etc.

• You mentioned that the Departmenats will be variable, Will the "- IN" match the "- OUT" per Rows.
• Will the Rows between each of the Departments "- IN" and "- OUT" be 2 Rows A18 & A19?
• Are all of the Departments Listed in the example workbook?

[Cutter]

@ Ashali

Welcome to the forum.

Please notice that code tags have been added to your post(s). The forum rules require them so please keep that in mind and add them yourself whenever showing code in any of your future posts. To see instructions for applying them, click on the Forum Rules button at top of the page and read Rule #3.
Thanks.

[Ashali]

Hi BuckoAk, yes these are external files with dynamic names that are defined within the macro file. I have tested the formula and it works.

As for my query, I assigned variables like x,y,z to reflect rows & columns containing values that matched the criteria and that has fortunately served the purpose.

Thanks for your help though.

[Ashali]

Noted. Thanks

@ Ashali

Welcome to the forum.

Please notice that code tags have been added to your post(s). The forum rules require them so please keep that in mind and add them yourself whenever showing code in any of your future posts. To see instructions for applying them, click on the Forum Rules button at top of the page and read Rule #3.
Thanks.