populating cells using data from a different worksheet

[dnganatra]

I have 21 columns with headings A, B, C, etc. with values between 0 to
11 in each cell. On a second worksheet, I have a table with
interpretations for High, Medium and Low for each of the column
headings (A, B, C, etc.)

High Medium Low
A A-Hi A-Med A-Low
B B-Hi B-Med B-Low
C C-Hi C-Med C-Low
etc.

I need to replace the values in worksheet one according to their
interpretations e.g. scores between 0 and 3 in the column A should
pull the text for A-Low, scores between 4 and 7 should pull the text
for A-Medium and scores between 8 and 11 should pull the text for A-
High. Similarly for the other columns.

Can anyone tell me the best way to do this? I am an absolute newbie
with no experience of writing macros or VBA.

Thanks,
Divyanshu

 

[Incidental]

Hi Divyanshu

The following code should do what you want or at least give you a
starting point to work with the code to get your desired results.

Open up VBE (Alt and F11)
Add a userform from the "Insert menu" then add a single command button
to the form the "toolbox" then in the code view paste the following
code

Option Explicit
Dim MyCell, MyRng As Range
Dim MyStr As String
Dim MyRow As Integer

Private Sub CommandButton1_Click()
Sheets(1).Activate 'Set the active sheet to sheet 1
MyRow = [A1].End(xlDown).Row 'Find last used row in the A Column
Set MyRng = Range("A1", "U" & MyRow) 'Set the range of cells you want
to change
For Each MyCell In MyRng 'Start to move through each cell in your
range
MyStr = MyCell.Address 'Take the cell address and pass it to a
string
MyStr = Mid(MyStr, 2, 1) 'Define the column letter from the
string
Select Case MyCell.Value 'Set the conditions for changing
the cell value
Case 0 To 3
MyCell.Value = MyStr & "-Low"
Case 4 To 7
MyCell.Value = MyStr & "-Med"
Case 8 To 11
MyCell.Value = MyStr & "-Hi"
End Select
Next MyCell 'Move on to the next cell in your range
End Sub

I would run this on a test sheet first just to see if this is what you
are after. If you want a better idea of how the code works when you
have pasted it press the F8 key to run the code line by line you will
be able to minimise VBE and see the effect on the cells as you move
through the code.

Hope this helps you out

S

 

[merjet]

This can be done w/o VBA. It assumes the letter headings are in row 1.
If that isn't true, put them there temporarily. To the right or below
the data put in one cell the formula =A$1 & "-" &
IF(A2<4,"Low",IF(A2<8,"Med","Hi")) Copy that cell and paste to a
block of cells the same size as your data, with the copied cell being
1st row, 1st column. Copy and Paste Special | Values the entire block
over the original data.

Hth,
Merjet

 

[Guest]

I don't think you need VBA instead do with formulas in Excel
I would have 3 worksheets labled Table,Scores, and Interpretation
Note the numbers 3,7, and 11 in 1st worksheet
Also empty cells in Column A in 2nd and 3rd worksheets. the blank cells in
column A lets all the data allign on the 3 sheets. I had to add the number
3,7, and 11 to get Vlookup to work

Table worksheet
A B C D E F thru L same as
previous

1 3 A-Low B-Low C-Low D-Low


2 7 A-Med B-Med C-Med D-Med


3 11 A-Hi B-Hi C-Hi D-Hi



Scores
A B C D E F thru L same as
previous

1 Scores Scores Scores Scores


2 Scores Scores Scores Scores


3 Scores Scores Scores Scores


Interpretation
A B C D E F thru L same as
previous

1

2

3

enter B1 and copy to B2 and B3
formula for B1 =vlookup(Scores!B1,Table!$A$1:$L$3,2,True)
Formula for B2 =vlookup(Scores!B2,Table!$A$1:$L$3,2,True)
Formula for B3 =vlookup(Scores!B3,Table!$A$1:$L$3,2,True)

Copy B1 to C1 thru L1
C1 change 2 to 3, then copy C1 to C2 and C3
Similar to above but "2,false" changes to "3,false"
formula for C1 =vlookup(Scores!C1,Table!$A$1:$L$3,3,True)
Formula for C2 =vlookup(Scores!C2,Table!$A$1:$L$3,3,True)
Formula for C3 =vlookup(Scores!C3,Table!$A$1:$L$3,3,True)

D1 change 2 to 4, then copy D1 to D2 and D3
Similar to above but "3,false" changes to "4,false"
formula for D1 =vlookup(Scores!D1,Table!$A$1:$L$3,4,True)
Formula for D2 =vlookup(Scores!D2,Table!$A$1:$L$3,4,True)
Formula for D3 =vlookup(Scores!D3,Table!$A$1:$L$3,4,True)

continue to column L is simialr fashion

 

[Guest]

I don't think you need VBA instead do with formulas in Excel
I would have 3 worksheets labled Table,Scores, and Interpretation
Note the number 3, 7, and 11 in column A.
I would leave column A empty on the other two worksheets so data is in the
same column on each worksheet.

Table worksheet
A B C D E F thru L
same as previous

1 3 A-Low B-Low C-Low D-Low


2 7 A-Med B-Med C-Med D-Med


3 11 A-Hi B-Hi C-Hi D-Hi



Scores
A B C D E F thru L
same as previous

1 Scores Scores Scores Scores


2 Scores Scores Scores Scores


3 Scores Scores Scores Scores


Interpretation (fil with formulas below table)
A B C D E F thru L same
as previous

1

2

3

enter B1 and copy to B2 and B3
formula for B1 =lookup(Scores!B1,Table!$A$1:$L$3,2,False)
Formula for B2 =lookup(Scores!B2,Table!$A$1:$L$3,2,False)
Formula for B3 =lookup(Scores!B3,Table!$A$1:$L$3,2,False)

Copy B1 to C1 thru L1
C1 change 2 to 3, then copy C1 to C2 and C3
Similar to above but "2,false" changes to "3,false"
formula for C1 =lookup(Scores!C1,Table!$A$1:$L$3,3,False)
Formula for C2 =lookup(Scores!C2,Table!$A$1:$L$3,3,False)
Formula for C3 =lookup(Scores!C3,Table!$A$1:$L$3,3,False)

D1 change 2 to 4, then copy D1 to D2 and D3
Similar to above but "3,false" changes to "4,false"
formula for D1 =lookup(Scores!D1,Table!$A$1:$L$3,4,False)
Formula for D2 =lookup(Scores!D2,Table!$A$1:$L$3,4,False)
Formula for D3 =lookup(Scores!D3,Table!$A$1:$L$3,4,False)

continue to column L is simialr fashion