Excel macro: enter data in each cell in a range based on the contents of another range's cells 1

[RBLampert]

This ought to be really simple but I can't figure out how to do it.

I want to create a macro that will check the (string) contents of the cells in one column, and based on the contents of each cell, enter a specific numerical value in the corresponding cell in a different row. The text strings, numerical values, and their correlations are all known, so the essential logic is, "if the string contents in cell An is such-and-such, enter this numerical value in cell En" (where A and E are the column designators and n is the row number).

The number of rows to be checked will vary each time I run this macro, but I've already got the code to make that work.

Appreciate the help!

 

[SkipVought]

I've gone back to look at your thread in more detail.

First, build the table with the run times loaded

[pre]
ModName RunTime
Web Reporting - Quarterly Activity Report (#359522133) 0:33
Creating the 2015 Annual Activity Plan (#384851572)
2015 Quarterly Activity Report Training (#266187068) 0:50
Web Reporting - Quarterly Financial Report (#738687927)
Maintaining 501(c)(3) Tax-Exempt Status (#295966425)
Logging onto AFA.org and Using Communities (#663180732)
AFA Organization Part 1 (#327636385)
Recruiting New Members Part 1 (#104705933)
Recruiting New Members Part 2 (#956869816)
Recruiting New Members Part 3 (#221373462)
Leadership Planning, Recruitment, and Retention (#919359449)
Building An Effective Community Partner Program (#288152105)
Building Effective Chapter and Base Relationships (#141988536)
[/pre]
The formula in column F would be...
[tt]
F2: =IFERROR(INDEX(RunTime,MATCH(A2,ModName,0),1),0)

...MATCH() returns the row offset within the ModName range, for the lookup value (A2)
...INDEX() returns the value at the row/column coordinates within the RunTime range
[/tt]
Notice that this is returning a 0 or the RunTime value rather than a zero length string. BAD idea to mix text and numbers in a column of data.

Also, I almost always would rather use INDEX/MATCH rather than VLOOKUP, as
1) I almost always use Named Ranges based on Column Headers and
2) INDEX/MATCH is much more versatile than VLOOKUP as the lookup column can be ANYWHERE in the table, while the lookup column must be the leftmost column for VLOOKUP range.

When you get to developing your process and workbook design, be sure to post another thread regarding a particular issue. You'll get lots of help here.

 

[RBLampert]

Thanks for the alternative. I tried the VLOOKUP option you suggested earlier and it worked great. I made sure the values to be drawn from the lookup table were in fact numbers and that the columns they were destined for were formatted properly. So far, so good. Now I've got two options. IMPORT will probably work well for bringing in the lookup table; I'm saving it in a separate workbook because it's also going to be changing from month to month as new modules are created and outdated ones removed.

I have a feeling I'm going to be spending lots of time on this forum, thanks to my rank amateur status! I also need to figure out how to copy a formula down a column whose length changes from month to month. Sure, I can manually double-click the cell handle, but I want to automate that process. Should be easy but my poor knowledge of VBA is preventing me from figuring it out on my own.

The other thing that's got me really puzzled (and I haven't poked around on this forum to see if there's an answer/explanation) is that macros I write seem to be available or unavailable depending on which workbook I open, even when I tell Excel to save them to the Personal.xlsb file. That's VERY frustrating! Something else to research.

REALLY appreciate your help so far.

 

[SkipVought]

I'm saving it in a separate workbook because it's also going to be changing from month to month [highlight #FCE94F]as new modules are created and outdated ones removed[/highlight].

What do you mean by [highlight #FCE94F]THIS[/highlight]?

I also need to figure out how to copy a formula down a column whose length changes from month to month.

If your data table is IMPORTED, the resulting QueryTable is in a ListObject which has a feature: formulas in the table adjust automatically as the number of rows change. And there is VBA code that also can do this.

macros I write seem to be available or unavailable depending on which workbook I open, even when I tell Excel to save them to the Personal.xlsb file.

Your PERSONAL.XLSB workbook always opens as a HIDDEN workbook in the background, whenever you open Excel. Whenever you write or change code in the PERSONAL.XLSB VBAProject, you must SAVE the PERSONAL.XLSB workbook as well as the workbook(s) you are working on. You should get a prompt to do this, but I try to SAVE before that happens.

...my rank amateur status!

...but my poor knowledge of VBA is preventing me from figuring it out on my own.

The other thing that's got me really puzzled...

Don't beat yourself up. EVERY VBA user has been where you are. I certainly was and yet there are still things that I don't know and need help with. So please feel at ease when posting your question. You seem that the kind of person that does their homework before asking for help. (" I haven't poked around on this forum to see if there's an answer/explanation...Something else to research.") This will help you in the long run! ;-)

 

[RBLampert]

Answers! What I'm referring to in the text you highlighted is this: This whole project is about monitoring how much a whole series of training modules, that a team of other folks and I are developing, are being used by our target audience. Some of these modules will become obsolete as the things they train on are changed or deleted--like how to fill out certain online forms. Those modules may or may not be replaced, depending on the exact situation. We also have a whole list of other modules that are in work or in line to be developed. So, the complete list of modules available to our audience will change over time. Each month, our organization's headquarters sends me a CSV file containing the usage data the training delivery system we use (called Brainshark) produces. Neither Brainshark nor anyone at our headquarters has the ability to do the analysis we want to do, so I'm setting up to do it. Believe it or not, everything I'm working on here is just to clean out the data we don't need, enter the data we do need that Brainshark doesn't provide, and get it ready to run through an analysis engine (that hasn't been developed yet!). I've been doing the analysis "manually" in Excel but now there's too much data coming in to do that and I don't have the time. So the long-term goal is to build that analysis engine--either in Excel or Access, or a combination of the two--so that ideally I can "push a button," the numbers get crunched, and the answers pop out. All of this on a volunteer, as time and knowledge permits basis.

If IMPORTing that data (from the CSV file, or saved into .xlsx format) will make copying the necessary formulas down the column to the end of the list, great, but that's all new to me and I don't know how to do it. Is there a good book or web site I can go to (besides this one, of course) to research this stuff, so I don't have to ask so many questions, or can ask better ones?

Saving the PERSONAL.XLSB file: ah-hah! Didn't know that! Whenever I open Excel now, that file opens as a blank workbook. Easy enough to hop on over to it and save it after each new macro gets saved. I've never been prompted to do that--because I'm working in a different file?

Finally, trust me, I'm not beating myself up. I'm happy to be learning this stuff. I just know I'm way down on the flat part of the learning curve and wish I wasn't. Your help, and that of the other folks on this forum, has been a life-saver more than once and I have NO problem with asking for it. In another part of my world I'm the one giving the help, not asking for it, so that lets me appreciate what you're doing even more.

Thanks again!

 

[SkipVought]

Monitoring how your employee population is using BrainShark. (My former firm has used this delivery system in the past). So I assume that you get statistics from BS, no pun intended, that you need to analyse. Excel is a good tool for data analysis.

I would caution against DELETING data that could be a valuable part of overall analysis. Include data for Module Status and dates that mark signicant milestones that will aid your analysis. Get familiar with good database design. Tables can be linked. Take care regarding data entry of duplicate data. Normalize where it makes sense.

Keep in mind that the term "module" has a specific meaning in VBA, that is different than your training modules. That's what raised my concern.

If you Google, Microsoft Query, you'll get some info and maybe a a video or 2. We can help here getting you started. It also can be a powerful analysis/reporting tool.

Regarding PERSONAL.XLSB, be certain exactly where you are coding! It could be in any of the open workbooks.

 

[RBLampert]

You're right, the data comes from Brainshark. And even though I do delete data, (a) it's not useful data--in fact, sometimes it's misleading!--and (b) irrespective of that, I do keep a separate copy of the original file.

You're right about the multiple meanings for the word "module!" Got to be careful about that!

I'll look into MS Query. I'm agnostic about what analysis platform I end up using, so long as (a) I can understand it well enough to use it effectively now, and (b) when it's time for me to hand this task off to someone else, I can either accelerate their climb up the learning curve or land them at the top right away.

Good reminder about PERSONAL.XLSB. Would it be possible to copy the VBA code created in one workbook into a Word document, then copy it again into the PERSONAL file? (I don't know why not.) Seems like that would help ensure everything got into that file. That might be a bit of a kludge, but it also provides a backup in the Word document.

 

[SkipVought]

Why copy code into Word?

Pay attention to the Project Explorer (ctrl+R). You can see in this window all workbooks open in your instance of Excel, including PERSONAL.XLSB. Here is where you could...
1) COPY code from one module to a module in another workbook, for instance your PERSONAL.XLSB.
2) MOVE a module, by dragging, from one workbook to another, with the caveat that the module name must be unique to the receiving workbook.

BTW I give all my modules meaningful names, other than Modulen.

Then you can "backup" your modules, all having unique and meaningful names by Exporting .bas files to a folder.

 

[RBLampert]

Not knowing about the Project Explorer, I was using Word as a way to achieve the same thing. It could still serve as another backup.

I do the same thing regarding naming modules. Seems like it would be silly not to.

 

[RBLampert]

I was looking for a way to get a formula set up in a macro so it could be copied down the full length of a column, when the length of the column (that is, the number of data fields) would change from month to month as new data came in. I discovered a thread on tables (#707-1670541) and thought I'd found the solution to many problems, especially after I created a macro with just such a formula that bases the actual value it calculates on the contents of another cell in that same row, and it worked!

So then I tried to do that again with a different formula, this time with a LOOKUP function. NO luck this time. Errors instead.

The first error was: Run-time error '1004': Application-defined or object-defined error.

This is the code that generated it:
Sub AddFullModTimetoTable()
'
' AddFullModTimetoTable Macro
' This macro is intended to enter the total module duration in H:MM:SS format into every row in the table.

ActiveCell.FormulaR1C1 = "=IFERROR(VLOOKUP(A2,TngModTotalData[#Data],2,FALSE),"")"
Range("F3").Select
End Sub

Somewhere I'd seen something that suggested maybe I needed to put quotes around A2, so I did. That got me this error: Compile error: Syntax error. OK, so that doesn't work.

Since part of that line of code says "FormulaR1C1," I tried changing A2 to RC[-5]--[-5] since the operative column was F and the reference column was A. That got me the same run-time error as above. "TngModTotalData" is the name of the table containing the references for the training module names and "#Data" is the special name for the data fields associated with those names. The file itself is attached to help make this all clear.

What am I doing wrong with that ActiveCell line of code?

 

[SkipVought]

When you get the error in AddFullModTimetoTable, exactly where is ActiveCell, ie which table/row/column is it in?

BTW, your formula works.

And, most importantly, if this formula is in a Structured table, there's no need for any macro!

 

[SkipVought]

BTW, here's your problem

ActiveCell.FormulaR1C1 = "=IFERROR(VLOOKUP(A2,TngModTotalData[#Data],2,FALSE),[highlight #FCE94F][b]""[/b][/highlight])"

Your formula returns a NUMBER that you format as Date/Time. You have coded your IFERROR to return an empty STRING. TILT!

You cannot have a string, even an empty string in a numeric column for Excel to work properly. Your IFERROR is the potential for a string. Hence the run error.

Rather...

ActiveCell.FormulaR1C1 = "=IFERROR(VLOOKUP(A2,TngModTotalData[#Data],2,FALSE),[highlight #FCE94F][b]0[/b][/highlight])"

If you don't want the 0:00 to display, use a Conditional Format to make the display appear to be empty (invisible font) by shading the font the same as the interior shade.

 

[RBLampert]

Ah, ha! As usual, it's an easy fix for someone who knows what they're doing. This distinction will be important since I think I'm going to use a similar macro that will call a string rather than a number later. Now I know code to use for that.

As for why I want this in a macro, the answer is the same as it's always been: I want to automate all of the processes I'm working on. Once I know each of the subroutines works individually, I plan to create one more that will call each of them in sequence so I can effectively click one button and have everything be done without manual intervention. That might not be entirely possible--I may have to manually enter the filename I want the modified file saved under--but as much as I can automate, that's what I'm after.