Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
populating cells using data from a different worksheet
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
populating cells using data from a different worksheet
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
populating cells using data from a different worksheet
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
populating cells using data from a different worksheet
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 " wrote: 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
populating cells using data from a different worksheet
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Populating Cells from One Worksheet to Another... | Excel Worksheet Functions | |||
Populating form with data from worksheet | Excel Programming | |||
Populating data to other worksheet via data validation | Excel Programming | |||
populating excel cells with data from data sources | Excel Programming | |||
Populating worksheet with data from Access | Excel Programming |