MS Excel VBA

Posted on 2013-08-29
Medium Priority
Last Modified: 2013-08-30
I need a macro to place value in column B after reading value in column. In the attached file I have set all the values in column B. The report will repeat the values in A and that is where I would like macro to Pick IC1 or IC2 or IC3 for columb. Can the values be hardcoded in the macro ?

e.g If col A= W03
       Then B = IC3
Question by:chitralekhaa
  • 2
LVL 26

Accepted Solution

redmondb earned 2000 total points
ID: 39452465
Hi, chitralekhaa.

Please attached. The code is...
Option Explicit
Option Base 0

Dim i As Long
Dim xLast_Row As Long
Dim xData As Variant
Dim xOutput As Variant


xLast_Row = ActiveSheet.UsedRange.Cells(1, 1).Row + ActiveSheet.UsedRange.Rows.Count - 1
If xLast_Row < 2 Then
    MsgBox ("No data found - run cancelled.")
    Exit Sub
End If

xData = Range("A2:A" & xLast_Row)
ReDim xOutput(1 To xLast_Row - 1)

For i = 1 To UBound(xData)
    Select Case xData(i, 1)
        Case "NE01", "NE02", "NE03", "NE04", "NE05", "NE06", "NE07"
            xOutput(i) = "IC1"
        Case "MW01", "SE01", "SE02", "SE03", "SE04", "SW01", "SW02"
            xOutput(i) = "IC2"
        Case "NW01", "NW02", "NW03", "W01", "W02", "W03"
            xOutput(i) = "IC3"
        Case Else
            xOutput(i) = "n/a"
    End Select

Range("B2:B" & xLast_Row) = Application.Transpose(xOutput)

End Sub

Open in new window


Author Closing Comment

ID: 39454020
Thank you so much for the this code. It is going to be a great help and you made it so easy.
LVL 26

Expert Comment

ID: 39454234
Thanks, chitralekhaa!

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

The System Center Operations Manager 2012, known as SCOM, is a part of the Microsoft system center product that provides the user with infrastructure monitoring and application performance monitoring. SCOM monitors:   Windows or UNIX/LinuxNetwo…
Microsoft's Excel has many features that most people will never need nor take advantage of.  Conditional formatting is one feature that you may find a necessity once you start using it.
Viewers will learn how to maximize accessibility options in an Excel workbook for users with accessibility issues.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
Suggested Courses

597 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question