• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

How to Match the Dates & Add column

KNMSRK

New Member
Hi,

Please can anybody help me out to get a VBA code for the below problem statement


Match the date in cell k4 in sheet 1 with Row #2( We have to match the date in entire row) in Avg Balance Sheet
upon matching the date, activate the cell and add a column next to active column ( ex after Aug-20) and name it as Sep-20
In Sheet 3 copy the values from Aug-2020 column and paste special in Jlu-20 column and rename the July-20 as Aug-20 ( which is I column)
Rename the cell J2 as Sep-20
 

Attachments

Let me rephrase and see how much of this I have right. I'm using R1C1 notation for clarity:

1) Look in Sheet2!R3C11 (you said K4, but I think you mean K3) for the date selected in the drop-down list.

2) Find that date in 'Average Balance sheet'!R2.

Let's pretend we found the target date in column 10. From there:

3) Insert a column in 'Average Balance sheet' after column 10, moving the already-existing columns 11 through whatever to the right.

4) Calculate a date one month later than the target date. Let's call that date TargetDatePlusOneMonth

5) Put TargetDatePlusOneMonth into 'Average Balance sheet'!R2C11 (the header cell of the new column).

5) Find the target date in Sheet3!R2.

Let's say it's found in column 10.

6) Copy the values in Sheet3!C10 over Sheet3!C9.

7) Write TargetDatePlusOneMonth over Sheet3!R2C10.

Is all that correct? Did I leave out anything? Did you leave out anything? (You don't want to clear out the old values in Sheet3!C10, for example?)
 

KNMSRK

New Member
Thank you for the reply Bob,

From the consecutive points listed above..... it is clear till 5th point
5) Find the target date in Sheet3!R2. - find the target date in sheet3 in row 2 as I want to keep the headers always in row2
we have data for august month in different columns, so I want to find out the target date in sheet3 row #2, then copy the data till last row as there are empty cell in the range ,from august month to July month including the header (i.e) Aug20 to July 20. then J2,M2 & P2 should change as Sep'20

In the Attached file, we have data for August(which is target date) . Please find attached the file and help me
 

Attachments

I see I misnumbered the options—I listed #5 twice. Sorry about that.

You say you want to find the target date in Sheet3, but in your latest workbook that date appears in three different columns. Should your program find the first matching date, or the last, or all of them, or what?

It sounds from your description as though you want the program to execute steps 5 through 7 for every instance of the target date in Sheet3!R2...?
 

KNMSRK

New Member
Hi Bob,

It sounds from your description as though you want the program to execute steps 5 through 7 for every instance of the target date in Sheet3!R2...?
- Yes It is Correct
I want to find out all the cells in sheet 3 where target date is available. In the spreadsheet(Sheet3) we have Aug-20 in 3 cells. Hence I want to copy the data from Aug-20 and paste it in before column (i.e) July column. Hope I am not confusing you
 
Well, let's look at the pieces of this, one at a time. You haven't said yet what your program look like, and what part isn't working, so I may end up telling you what you already know. But let's start here:
Code:
' I almost never work with drop-down lists in Excel, and have never tried to make them work with VBA.  So I'm
' going to pretend that once a user has selected the value in the list, it's available as that cell's Value property.
Set owb = ThisWorkbook
Set ows1 = owb.Worksheets("Sheet1")
DropDate = ows1.Cells(3, 11).Value 'row 3, column 11

' Now look for DropDate in Average Balance sheet.  You can use the Excel Find method, but here I'll show you how to do it manually:
Set owsA = owb.Worksheets("Average Balance sheet")
For jc = 1 to 26 'arbitrarily saying we won't look further than column Z
  ' Look in row 2, columns A through Z:
  If owsA.Cells(2, jc).Value = DropDate Then Exit For
  Next jc
If jc > 26 Then 'it went all the way from A to Z without finding that date
  MgsBox "Target date " & DropDate & " not found in " & owsA.Name & "."
  Exit Sub
  End If

' Now jc is the column where we found DropDate.  Do the Insert.
owsA.Columns(jc + 1).Insert

' Next we have to calculate a date that's one month later than DropDate.  But let's pause here to breathe.
Maybe you had this part of your program working already, and the above is unnecessary. Or maybe parts of the above are unclear and you need to ask questions. I'll let you respond and this point, and we can do more later.
 
Top