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!

 

[Andrzejek]

Something like:

For intRow = 2 To intLastRow
    Select Case Range("A" & intRow).Value
        Case "ABCD"
           Range("E" & intRow).Value = 12
        Case "XYZ"
           Range("E" & intRow).Value = 678
        Case "KLMN"
           Range("E" & intRow).Value = -23.765
        ...

    End Select
    intRow = intRow + 1

Next intRow

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[SkipVought]

I'd have asked the programmer, what code he had so far and where he's stuck.

 

[SkipVought]

This is all rather nebulous.

For instance, "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."

Beyond not knowing where you are in your coding, I just don't know what that means.

 

[SkipVought]

@Andy,

You should never change the loop index inside a For...Next loop! It's already indexing by one by default.

 

[zelgar]

Why do you even need a macro? A simple IF statement will do the trick (e.g., formula in E cells =IF(A2="ABC",1,"") )

 

[Andrzejek]

Ooops! Skip, you are right on both counts.
I should ask where the OP is in the coding and where is stuck, and
no incrementing the counter inside For Next loop. (I was thinking .MoveNext with the recordset...:-( )

zelgar,
If there are 28 different string/number scenarios, the IF statement would be pretty long.


Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[SkipVought]

Andy, where did 28 come from?

I agree that is MAY be that it could be a spreadsheet formula, with a table lookup, BUT that's Judy a guess cuz we STILL don't know enough from the OP's vagueness, and we ALL know that...

...what happens in vagueness, stays in vagueness!

 

[RBLampert]

Thanks, for the responses, guys. To answer zeglar's question first, I want/need the macro because there are too many possible "if" conditions. Yes, I suppose I could make this massive list of nested "if" functions and copy it into the hundreds of Column E cells, but that's a kludge at best. A macro would be cleaner and simpler.

As for the code. I've inserted the two TEST versions of the macro below. Both tests are truncated versions of what the full macro would be; I wanted to make sure it would work (or fail) in just a few test cases before going to the effort of creating the full macro.

Keep in mind that I am NOT a professional or even active hobbyist programmer. I'm a rank amateur and I know it. In fact, this is the first time I've tried to write Excel macros, and the books and online references I've looked at have helped, but only a little. But I do know that a macro is the right approach for what is going to be a monthly task that is only going to get bigger and bigger over time.

OK, here's the first test code:

Sub InsertModuleDuration()
'
'This macro is meant to insert the correct run time for each module in the "FullTime" column based on the module name found in Column A.
'At the moment it inserts the 2015 QAR total time in all cells in Column F ("FullTime"), irrespective of what module name is in Column A.
'
Dim ModNameArray(12)
Dim ModName
ModName = Array("Web Reporting - Quarterly Activity Report (#359522133)", "Creating the 2015 Annual Activity Plan (#384851572)", "2015 Quarterly Activity Report Training (#266187068)", "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)")
FinalRow = Cells(Rows.Count, 1).End(xlUp).Row
Range("A2", Cells(FinalRow, 1)).Value = ModName
For x = 2 To FinalRow
Select Case Cells(x, 1).Value
Case "2015 Quarterly Activity Report Training (#266187068)"
Cells(x, 6) = 3007 / 86400
Case "Web Reporting - Quarterly Activity Report (#359522133)"
Cells(x, 6) = 1990 / 86400
End Select
Next x
MsgBox "Macro Complete"

End Sub

Here's the code for the second test:

Sub InsertModDuration2()
'
'This macro is a test using If... Then logic to try to get the correct times displayed in the "FullTime" column based on the module name found in Column A.
'This code blanks columns A and E (H:MM:SS; the viewing time in hours:minutes:seconds format). I have no idea why it's blanking column E.
'

Dim ModName As String
' ModName = Array("Web Reporting - Quarterly Activity Report (#359522133)", "Creating the 2015 Annual Activity Plan (#384851572)", "2015 Quarterly Activity Report Training (#266187068)", "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)")
FinalRow = Cells(Rows.Count, 1).End(xlUp).Row
Range("A2", Cells(FinalRow, 1)).Value2 = ModName
For x = 2 To FinalRow
If ModName = "Web Reporting - Quarterly Activity Report (#359522133)" Then
Cells(x, 6) = 3007 / 86400
End If
Next x
MsgBox "Macro Complete"

End Sub

Hope this helps. I appreciate your feedback.

 

[SkipVought]

Holy macro, Batman!

Responding on my iPhone...

I would DEFINITELY use a lookup table. No VBA!
[tt]
=IFERROR(VLOOKUP(E2,YourLookupRamge,2,FALSE),"")
[/tt]

 

[SkipVought]

My formula assumes row2 is first row of data.

 

[Andrzejek]

20 was just a guess, but looks like I was pretty close. RBLampert's array ModName has 13 elements

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[RBLampert]

Andy, over time, 20 is going to be closer to correct, maybe even LESS than the final number!

Skip, you're correct. Row 2 is the first one with data. The =IFERROR(VLOOKUP... combination looks promising. I'll still have to figure out how to do some other things in support of this, but it's got potential.

 

[SkipVought]

Over time" the list will evolve.

All the more reason to use a table rather than hard coded values either in a formula or in a macro! This follows best and accepted practices.

 

[RBLampert]

Good to know, Skip. Thanks.

 

[Andrzejek]

In my opinion, Skip deservers a Star for his help.
Please, use “Great Post? Star it” link to show appreciation for help received.


Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[RBLampert]

I agree, Andy! Done!

Skip, I would never have thought of using the IFERROR and VLOOKUP functions. I also discovered that I can double-click on the cell "handle" and the formula in one cell will copy all the way down the column to the last populated row. Very convenient!

 

[SkipVought]

I'm a VBA guy, but would rather use spreadsheet features first when that makes sense.

Glad you found the handle feature.

 

[RBLampert]

Yeah, I understand, Skip. In this case, though, I want/need to automate as much as I can. Ultimately, I want to be able to open the data file I receive from an outside source, start one macro (which calls a bunch of others in sequence), and watch magic happen, ending with the data formatted and calculated the way I need it. This is all prep work for further analysis... but one step at a time.

 

[SkipVought]

I've often done this using a pre-designed workbook.

IMPORT the data from the outside source via a query that already is formatted.