Converting a forumla into a macro function

[-Wayner-]

Hi all,

Wonder if someone may be able to help me a little. Put simply i am writting a series of macro operations that copy data from one sheet to another.

The code looks something like:

Sheets("Mm Unders & Overs by Div").Select
Range("E67:E71").Copy
Sheets("Corp Month on Month Summary").Select
Range("L17").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

But i want Cell L17 to change based on the date i have in another cell.


The way i thought of doing this is how i would do it if it was a simple formula i.e.

Created a reference area called 'sumdates' which has the date in column one and the corresponding column letter in the second column i.e.

col1 - col2
P1 Wk1 - L
P1 WK2 - N
P1 Wk3 - P

Assuming the cell containing the date is just called 'date' my formula would therefore be (bearing in mind that the 17 i.e row number never changes):

=indirect(vlookup(date,sumdates,2,0)&"17")

which would simply replace the "L17".

Trouble is i don't know where to start in making this a macro function, or if it can be done.

Any advice on the matter would be very much appreciated.

Thank you kindly in advance.

 

[NdNoviceHlp]

No need for selection. This should get you started. Not sure about the rest as it is somewhat unclear. HTH. Dave

Application.ScreenUpdating = False
Sheets("Mm Unders & Overs by Div").Range("E67:E71").Copy
Sheets("Corp Month on Month Summary").Cells(17, "L").PasteSpecial _
Paste:=xlPasteAll, Transpose:=False
Application.CutCopyMode = False
Application.ScreenUpdating = True

 

[-Wayner-]

Hi,

Thanks for the response, but i'm not sure this is doing quite what i am looking for. I need the 'L' in the initial code to change depending on the contents of the cell 'date'.

Put crudely, in my limited knowledge of VBA i am trying to replicate something along the lines of:

Cells(17, "=vlookup(date,summary_periods,2,0)").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False


With the lookup allowing me to change the column that is pasted into depending on the contents of the 'date' cell.

Is there a way of phrasing this so it actual work, as the above in all incarnations i can think of just throws me errors? Or do i need to think of perhaps another way of solving this issue?

Ideas would be much appreciated again.

Thanks,
Wayne

 

[NdNoviceHlp]

"Is there a way of phrasing this so it actual work, as the above in all incarnations i can think of just throws me errors?"??? The code posted copies your specified range and pastes it to your specified L17 location. What cell is the date in? How is the date evaluated to determine it's pasted location? What are the pasting locations? The code can be adjusted to accomodate all of this but you need to specify what your exact needs are. Dave

 

[-Wayner-]

Thanks agan for the help Dave, sorry for not being all that clear. I'll take this back a notch and try see if i make a little more sense !

I have some data in the sheet "Mm Unders & Overs by Div" cells "E67:E71".

I am looking to automate a function that copies the contents of those cells (values only) into my next sheet "Corp Month on Month Summary".

The catch is that i want to specify a different column depending on the contents of a cell in the second sheet, which for the sake of clarity lets say i have named that cell 'ThisWeek'

So for example one week i will run the macro and the contents of 'ThisWeek' will be 'P1Wk1' therefore i would like the data from the first sheet copied into column 'L' in my second sheet (or more precisely the cells starting from 'L17' down). The 17 is always the same, i.e. the first line of data will always be posted into the 17th row.

The next week i will change the contents of 'ThisWeek' to say 'P1Wk2'. In this instance i would like my data from the first sheet to be copied into cell 'N17' (or more precisely the cells starting from 'N17' down).

So on and so forth for a lengthy set of time periods. As the specified date value (what I refer to as PxWy) increases the data wll continue to be pasted further along the sheet.

I will be duplicating this process over a larger amount of data and carrying it out weekly, thus why i would really like to automate it but for now if i can just nail down the basic idea of how to do it I should be able to expand this to suit my needs.

Going back to my initial explanation, my first thought on how to do this would be to simply use some form of formula to replace the value 'Range("L17")' with the relevant corresponding value depending on what week i am in, creating a table that tallies weeks to column letters and then using a lookup from this, but that is what i can't get to work and thus why i am stumped.

It's allmost as if i want the sheet to think of each column as a different week and let me select which week i am in so i paste the new information into the right column.

I really hope this is a little bit clearer now.

Thanks again for any help, it is very much appreciated!

Cheers
Wayne

 

[NdNoviceHlp]

Wayne if in each succesive week the column increases by one, could you not just increase the "Cells" column reference by one (or 2)? (What happenned to column "M" or do you want to skip a column every week?) If you need to specifically name and match each column with each week it will be a whole lot of tedious code. It is fairly easy to determine the next "available" column to the right of your pasted stuff starting in "L17". Maybe something like this providing there is no data to the right of your paste? Dave

Dim LastCol As Integer
With Sheets("Corp Month on Month Summary")
LastCol = .Cells(17, .Cells.Columns.Count).End(xlToLeft).Column()
End With
Sheets("Corp Month on Month Summary").Cells(17, LastCol + 1).PasteSpecial _
Paste:=xlPasteAll, Transpose:=False

 

[-Wayner-]

This does the trick just great, i just changed the lastCol+1 to Lastcol +2 to suit the spreadsheet, but is exactly the sort of thing i was looking for, thanks.

Just one more final question if i may...

If i wanted to use the lastcol function as above over a multiple of cells how would i phrase it?

For example if 'Cells("L17") = Cells(17,LastCol+2)' [in this instance]

then what would 'Range("L10:L49")' equate to using the same principle?

Thanks again for the support, much appreciated.

 

[NdNoviceHlp]

Not sure if I understand. Here's the syntax I think you will need. The 17 can be replaced by any variable integer (ie.cnt) which can also be used to specify any row within the pasted range. That's the beauty of using "Cells" in VBA. HTH. Dave

Sheets("Corp Month on Month Summary").Range _
 (.Cells(17, LastCol + 2), .Cells(17, LastCol + 2))

edit: I'll rework that..it's wrong

 

[-Wayner-]

Not sure if I understand. Here's the syntax I think you will need. The 17 can be replaced by any variable integer (ie.cnt) which can also be used to specify any row within the pasted range. That's the beauty of using "Cells" in VBA. HTH. Dave

Sheets("Corp Month on Month Summary").Range _
 (.Cells(17, LastCol + 2), .Cells(17, LastCol + 2))

edit: I'll rework that..it's wrong

Variable integer? that sounds like a prominsing element... so instead of 17 i replace with cnt?

 

[NdNoviceHlp]

Here's some more syntax things. Maybe you'll be able to adapt for your needs. Dave

Private Sub CopyIt()
Dim Lastrow As Integer, Rng As Range, C As Variant, Cnt As Integer
Dim LastCol As Integer
Application.ScreenUpdating = False
Sheets("Mm Unders & Overs by Div").Range("E67:E71").Copy
With Sheets("Corp Month on Month Summary")
LastCol = .Cells(17, .Cells.Columns.Count).End(xlToLeft).Column()
Application.ScreenUpdating = False
Sheets("Corp Month on Month Summary").Cells(17, LastCol + 2).PasteSpecial _
Paste:=xlPasteAll, Transpose:=False
'pasted data location
Lastrow = .Cells(.Cells.Rows.Count, LastCol + 2).End(xlUp).Row()
Set Rng = .Range(.Cells(17, LastCol + 2), .Cells(Lastrow, LastCol + 2))
End With
'For Each C In Rng
'MsgBox C
'Next C
'For Cnt = 17 To Lastrow
'MsgBox Sheets("Corp Month on Month Summary").Cells(Cnt, LastCol + 2)
'Next Cnt
Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub