Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Hi Alllen
Hi Allen,
Thanks for your initiative and solution :) Really appreciate everyone's support and contribution ot the forum/community. I'm back again with the qruery on the same topic/challenge for me :) This time..as you suggested I'm more descriptive...I'm describing my problem/challenge below 1. I have a sheet with 3 columns (MULTIPLE ROWS..Row # will change from project to project so Row number is dynamic/generic) 2. Col A has some value say..Months/Years (NOT restricted to 12 numbers). 3. Col B, Col C have some data. 4. User will be prompted to select a ROW from the row number specified on the left hand side of the work sheet (Using--Set Range = Application.InputBox(prompt:="PLEASE CLICK ON THE ROW NUMBERS LISTED on THE LEFT HAND SIDE TO SELECT A ROW", Type:=8) 5. Suppose user selects Row# 5, then the data in Row#2 of Column A (neglecting row#1 which is column title) which is A2, the data in Row#5 of Column A (neglecting row#1 which is column title) which is A5, (ROW WHICH WAS SELECTED BY THE USER) the data in last Row#n of Column A will be copied and pasted in the respective cells under column D (that is D2, D5 and Dn) 6. Since User had selected Row#5 above.. the data in Row#2 of Column B till Row#5 of Col B will be selected/copied (that is values in B2, B3, B4 and B5) and pasted in Row#2 though Row#5 of Column E respectively. 7. Since User had selected Row#5 above.. the data in Row#5 of Column B till Row#n of Col B will be selected/copied (that is values in B5, B6....Bn) and pasted in Row#5 though Row#n of Column F. 8. Since User had selected Row#5 above.. the data in Row#2 of Column C till Row#5 of Col C will be selected/copied (that is values in C2, C3, C4 and C5) and pasted in Row#2 though Row#5 of Column G respectively. 9. Since User had selected Row#5 above.. the data in Row#5 of Column C till Row#n of Col C will be selected/copied (that is values in C5, C6....Cn) and pasted in Row#5 though Row#n of Column H. What my code does not do Step7, Sept9, and Step5 partly. I have taken care of column titles in my code (but not have explained above) they can be ignored..I'm OK with data copying and not with column titles.. ******************************* Sub Test() ' ' ' Let user select a row of values by clicking on the row number listed on the work sheet Dim Rng As Range On Error Resume Next Set Rng = Application.InputBox(prompt:="PLEASE CLICK ON THE ROW NUMBERS LISTED on THE LEFT HAND SIDE TO SELECT A ROW", Type:=8) If Rng Is Nothing Then MsgBox "Operation Cancelled" Else Rng.Select With Selection.Interior .ColorIndex = 7 .Pattern = xlSolid End With '''''''''''''''''''''''''''' 'Populating project date fields from column A Dim kLastRow As Long Dim k As Long kLastRow = Cells(Rows.Count, "A").End(xlUp).Row If Not Rng Is Nothing Then Range("A2").Copy Range("E2") Rng.Copy Cells(Rng.Row, "E") 'Cells(kLastRow, "A").Copy Cells(kLastRow, "E") End If '''''''''''''''''''''''''''''''''' 'Populating column F and Col G Dim jLastRow As Long Dim j As Long jLastRow = Cells(Rows.Count, "B").End(xlUp).Row Range("B1").Resize(Rng.Row).Copy Range("F1") Rng.Offset(1, 0).Resize(iLastRow - Rng.Row).Copy Range("G2") 'Populating column H and Col I Dim lLastRow As Long Dim l As Long lLastRow = Cells(Rows.Count, "C").End(xlUp).Row Range("C1").Resize(Rng.Row).Copy Range("H1") Rng.Offset(1, 0).Resize(iLastRow - Rng.Row).Copy Range("I2") End If End Sub ******************************************** I have a table (which is dynamic and will change from Project to Project so I can NOT HARD CODE THE MACRO) Col A Col B Col C Month Planned Actual Row# 1 Jan 50 48 Row# 2 Feb 55 54 Row# 3 Mar 58 60 Row# 4 Apr 60 62 Row# 5 May 65 65 Row# 6 Jun 68 65 Row# 7 Jul 75 70 Row# 8 Aug 85 84 Row# 9 Sep 100 95 Row# 10 2. User runs the macro and the required out put is Col A Col D Col E Col F Col G Jan 50 48 55 54 58 60 Apr 60 62 60 62 65 65 68 65 75 70 85 84 Sep 100 95 *********************************************** |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Hi Alllen
Try this:
Sub Test() ' ' ' Let user select a row of values by clicking on the row number listed 'on the work sheet Dim Rng As Range On Error Resume Next Set Rng = Application.InputBox(prompt:="PLEASE CLICK ON THE ROW NUMBERS LISTED on THE LEFT HAND SIDE TO SELECT A ROW", Type:=8) If Rng Is Nothing Then MsgBox "Operation Cancelled" Else Rng.Select With Selection.Interior .ColorIndex = 7 .Pattern = xlSolid End With '''''''''''''''''''''''''''' 'Populating project date fields from column A Dim ilastrow As Long ilastrow = Cells(Rows.Count, "A").End(xlUp).Row If Not Rng Is Nothing Then Range("A2").Copy Range("E2") Range("A" & Rng.Row).Copy Range("E" & Rng.Row) Range("A" & ilastrow).Copy Range("E" & ilastrow) ' End If '''''''''''''''''''''''''''''''''' 'Populating column F and Col G Range("b2").Resize(Rng.Row - 1, 2).Copy Range("F2") 'Populating column H and Col I Range("B" & Rng.Row).Resize(ilastrow - Rng.Row + 1, 2).Copy Range("H" & Rng.Row) End If End Sub "Prakash" wrote: Hi Allen, Thanks for your initiative and solution :) Really appreciate everyone's support and contribution ot the forum/community. I'm back again with the qruery on the same topic/challenge for me :) This time..as you suggested I'm more descriptive...I'm describing my problem/challenge below 1. I have a sheet with 3 columns (MULTIPLE ROWS..Row # will change from project to project so Row number is dynamic/generic) 2. Col A has some value say..Months/Years (NOT restricted to 12 numbers). 3. Col B, Col C have some data. 4. User will be prompted to select a ROW from the row number specified on the left hand side of the work sheet (Using--Set Range = Application.InputBox(prompt:="PLEASE CLICK ON THE ROW NUMBERS LISTED on THE LEFT HAND SIDE TO SELECT A ROW", Type:=8) 5. Suppose user selects Row# 5, then the data in Row#2 of Column A (neglecting row#1 which is column title) which is A2, the data in Row#5 of Column A (neglecting row#1 which is column title) which is A5, (ROW WHICH WAS SELECTED BY THE USER) the data in last Row#n of Column A will be copied and pasted in the respective cells under column D (that is D2, D5 and Dn) 6. Since User had selected Row#5 above.. the data in Row#2 of Column B till Row#5 of Col B will be selected/copied (that is values in B2, B3, B4 and B5) and pasted in Row#2 though Row#5 of Column E respectively. 7. Since User had selected Row#5 above.. the data in Row#5 of Column B till Row#n of Col B will be selected/copied (that is values in B5, B6....Bn) and pasted in Row#5 though Row#n of Column F. 8. Since User had selected Row#5 above.. the data in Row#2 of Column C till Row#5 of Col C will be selected/copied (that is values in C2, C3, C4 and C5) and pasted in Row#2 though Row#5 of Column G respectively. 9. Since User had selected Row#5 above.. the data in Row#5 of Column C till Row#n of Col C will be selected/copied (that is values in C5, C6....Cn) and pasted in Row#5 though Row#n of Column H. What my code does not do Step7, Sept9, and Step5 partly. I have taken care of column titles in my code (but not have explained above) they can be ignored..I'm OK with data copying and not with column titles.. ******************************* Sub Test() ' ' ' Let user select a row of values by clicking on the row number listed on the work sheet Dim Rng As Range On Error Resume Next Set Rng = Application.InputBox(prompt:="PLEASE CLICK ON THE ROW NUMBERS LISTED on THE LEFT HAND SIDE TO SELECT A ROW", Type:=8) If Rng Is Nothing Then MsgBox "Operation Cancelled" Else Rng.Select With Selection.Interior .ColorIndex = 7 .Pattern = xlSolid End With '''''''''''''''''''''''''''' 'Populating project date fields from column A Dim kLastRow As Long Dim k As Long kLastRow = Cells(Rows.Count, "A").End(xlUp).Row If Not Rng Is Nothing Then Range("A2").Copy Range("E2") Rng.Copy Cells(Rng.Row, "E") 'Cells(kLastRow, "A").Copy Cells(kLastRow, "E") End If '''''''''''''''''''''''''''''''''' 'Populating column F and Col G Dim jLastRow As Long Dim j As Long jLastRow = Cells(Rows.Count, "B").End(xlUp).Row Range("B1").Resize(Rng.Row).Copy Range("F1") Rng.Offset(1, 0).Resize(iLastRow - Rng.Row).Copy Range("G2") 'Populating column H and Col I Dim lLastRow As Long Dim l As Long lLastRow = Cells(Rows.Count, "C").End(xlUp).Row Range("C1").Resize(Rng.Row).Copy Range("H1") Rng.Offset(1, 0).Resize(iLastRow - Rng.Row).Copy Range("I2") End If End Sub ******************************************** I have a table (which is dynamic and will change from Project to Project so I can NOT HARD CODE THE MACRO) Col A Col B Col C Month Planned Actual Row# 1 Jan 50 48 Row# 2 Feb 55 54 Row# 3 Mar 58 60 Row# 4 Apr 60 62 Row# 5 May 65 65 Row# 6 Jun 68 65 Row# 7 Jul 75 70 Row# 8 Aug 85 84 Row# 9 Sep 100 95 Row# 10 2. User runs the macro and the required out put is Col A Col D Col E Col F Col G Jan 50 48 55 54 58 60 Apr 60 62 60 62 65 65 68 65 75 70 85 84 Sep 100 95 *********************************************** |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|