0

I am super inexperienced with VBA. What I've accomplished with VBA I've picked up by Googling. So, there's no depth, at all, to what I know.

OK, here's what I have:

In a workbook I have two sheets. Lets call them Sheet1 and Sheet2.

In Sheet1 I have this 2-D table, where the ###s are some value corresponding to the matching row and column labels:

Age     Males    Females
18-29   #####    ######
30-44   #####    ######
45+      #####   ######

The cells for all of the above are D2:F5

Now, in Sheet2 I have 3 columns: ID Age Sex which start at A7. So, the headers are in A7, B7, C7

You can probably see where I'm going with this.

I have thousands of rows.

I need to place the appropriate ### in column D7, with the header Amount based on matching values in Sex (B) and Age (C).

So, if the cross-tab in Sheet1 had these values:

Age     Males       Females
18-29   123        456
30-44   789        101112
45+      131415   161718

Then in Sheet2 I'd like to see:

ID   Sex          Age       Amount
1    Female    30-44     101112
2    Male        18-29     123

etc on down the column.

And I don't have the slightest clue how to go about this. My biggest VBA accomplishment so far is importing a CSV file into an active worksheet, so...

JeniFav
  • 113
  • 1
  • 9
  • https://stackoverflow.com/questions/20541905/convert-matrix-to-3-column-table-reverse-pivot-unpivot-flatten-normal – Slai Aug 17 '17 at 00:52

1 Answers1

0

One question is: Is "Sex" and "Age" manual inputs in the Sheet2?

You might want to use the function:

Application.Worksheetfunction.Vlookup(...)

But of course, you need to set some type of identification for each Sheets such as :

Dim wSheet1 as Worksheet
Dim wSheet2 as Worksheet
Set wSheet1 = Worksheets("Sheet1")
Set wSheet2 = Worksheets("Sheet2")

Just like how you would use it in normal workbook.

Example:

wSheet2.cells("A7").value = "1" 'Note: You can improve this by looking at an empty row automatically with a code
wSheet2.cells("B7").value = application.worksheetfunction.vlookup("18-29",wSheet1.Range("D4:F9"),3,0)

You can improve the program to your liking by editing the search criteria "18-29"

Cheers

Jem Eripol
  • 225
  • 1
  • 13
  • Hi! Thanks for responding. Sex and Age are actually calculated from other data, so they're formula not values. I was worried about Vlookup because the number of rows in Sheet2 will change frequently. Right now I have a button on Sheet2 that imports a CSV file that's output from Stata. Then I essentially want to "merge" the age and sex aggregated data for Amount to this CSV file. I could easily do this in Stata but I am trying to make an Excel "calculator" for folks I work with zero coding skills (even in something like Stata). I'll see if I can make you suggestion work. Thanks! – JeniFav Aug 17 '17 at 14:19
  • If I get your problem here, your range in vlookup is "not fixed", right? If the number of columns is fixed and the number of row is not, you can specify the number of rows by making the program look for the bottom row such that `Dim iRow As Long` `Dim ws As Worksheet` `Set ws = Worksheets("1")` `Dim wbSource As Workbook` `iRow = ws.Cells.Find(What:="*", SearchOrder:=xlRows, _` `SearchDirection:=xlPrevious, LookIn:=xlValues).row + 1` – Jem Eripol Aug 17 '17 at 23:53
  • 1
    You can also find the last row by `lastrow = ws.UsedRange.Rows (ws.UsedRange.Rows.Count).Row` instead of using Find. Or better yet, after importing the data, turn it into a table and use structured references so you don't have to worry about the last row and could even just have formulas on the aggregate sheet instead of filling it with VBA. :-) – Erin Halbmaier Aug 18 '17 at 00:15
  • Oh, this one last comment is better, i guess! :) thank you for suggesting. – Jem Eripol Aug 18 '17 at 00:18