none
Repeat cell value with said value and auto adjust data accordingly RRS feed

  • Question

  • 

    Respected Guru’s

     

    in this sheet,   I want that repeat cell value with given figure(which is in column N in sheet 2) in sheet 1 and also adjust these value in columns as I mention in sheet 1 accordingly and others will remain same and auto appear in their columns, sample sheet as in below link and all data should convert in upper case..!!

    i share only sample data it can be large and differ...

    need your kind help on this.

    https://www.dropbox.com/s/zmflt7aqqfp0t0s/PROMSAT%2050%20-%20JAN.xlsx?dl=0

     

     

    Nabeel Gondal



    Thursday, February 2, 2017 2:36 PM

Answers

  • Dear Nabeel

    You can call UCase method to convert the date like this

    Sheets("Sheet1").Cells(M, "S") = UCase(Sheets("Sheet2").Cells(i, "J").Value)
       Sheets("Sheet1").Cells(M, "W") = UCase(Sheets("Sheet2").Cells(i, "J").Value)

    • Marked as answer by Nabeel Gondal Tuesday, February 7, 2017 5:08 AM
    Tuesday, February 7, 2017 1:24 AM

All replies

  • Hello All

    pl can any one help me out..???

    Nabeel

    Friday, February 3, 2017 4:59 AM
  • Hi,

    To convert all characters into upper cases, you could use code below:

    Sub UpperCase()
    For Each sht In ActiveWorkbook.Sheets
    For Each cell In sht.UsedRange.Cells
    cell.Value = UCase(cell.Value)
    Next cell
    Next sht
    End Sub

    Does sheet2 show your expected result?

    According to the existing records in sheet2, I think you want to count duplicated SALES_I. In sheet2, SIM_NO re-starts from 780961846000, the rest columns are the same in corresponding columns in sheet1.

    Regards,

    Celeste


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.



    Friday, February 3, 2017 5:38 AM
    Moderator
  • dear Celeste

    no dear, i want to get result in sheet 1 , i will enter data in sheet 2 than will run macro result shows in sheet 1 as per column N value,  means if i write 5 in column N in sheet 2 that rows should repeat 5 time in said cells in sheet 1, i have made the example in given workbook for better understanding in real case that sheet 1 only has header...!! results are in sheet 1 according to sheet 2.

    Nabeel Gondal





    Friday, February 3, 2017 5:47 AM
  • Hi,

    You could write code in Worksheet_change event, so when we change the value in column REPEAT, fill the data in sheet1 based on the value.

    E.g.

    Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Column = 14 Then
    Dim usedRow As Integer
    usedRow = Sheets("Sheet1").UsedRange.Rows.Count
    If Target.Value <> "" Then
    For i = 1 To Target.Value
    Sheets("Sheet1").Cells(usedRow  + i, "N") = Target.Offset(0, -7).Value 'SALES_I
    Sheets("Sheet1").Cells(usedRow  + i, "S") = Target.Offset(0, -4).Value 'F_N_SUB
    Sheets("Sheet1").Cells(usedRow  + i, "W") = Target.Offset(0, -4).Value 'FULL_N_SUB
    Sheets("Sheet1").Cells(usedRow  + i, "X") = Target.Offset(0, -3).Value 'DIS_PRO
    Sheets("Sheet1").Cells(usedRow  + i, "AC") = Target.Offset(0, -1).Value 'ID_NO_SUB
    Next
    End If
    End If
    End Sub

    Regards,

    Celeste


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.



    Friday, February 3, 2017 6:29 AM
    Moderator
  • Dear Celeste

    i pasted above code in sheet  and when i hit on macros for run nothing is showing to run..

    Nabeel 

    




    Friday, February 3, 2017 6:45 AM
  • Hi,

    Open your file,

    Open the VBA Editor (Alt+F11)

    Open Sheet2(Sheet2) and paste the code above.

     

    Close the VBA Editor.

    In Sheet2, change the record in column N, then you could find the new records in sheet1 are generated based on the value.

     


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, February 3, 2017 7:34 AM
    Moderator
  • Dear Celeste

    some of working as per my requirement. as under is not pl hlep

    1, when i change the figure in column N this is adding more rows in previous figure it should not

    2, as per sheet 2 column A to F & column L & I, E is data is not showing in sheet 1.

    3, in sheet 1 column C,D,I,K,U,V,T,o,p will remain blanks as it is.(may its correct now)

    4, in sheet 1,column F,H,M,Q,Z,AA,AB data will remain as it is and show as when VBA run..

    looking forward for your kind help

    Nabeel 


    Friday, February 3, 2017 8:46 AM
  • Excel 2010/2013/2016 Power Query (aka Get & Transform)
    No formulas, no VBA macro.
    With Tables (not cell references)
    With US styles and data.
    Recommended for advanced users.
    http://www.mediafire.com/file/mcs3iiw5p2q2bhg/02_03_17.xlsx

    Friday, February 3, 2017 7:15 PM
  • dear Herbert

    i do not have much idea about it, i downloaded power query tool but its not showing in ribbon so i cannot make changes as per my need...??

    Nabeel 


    Saturday, February 4, 2017 10:25 AM
  • You will not be able to make changes as per your needs
    until you learn Power Query, a task as big as Excel itself.
    Recommended book:
    "The Excel Pro's Definitive Guide to Power Query"
    by Ken Puls.

    Saturday, February 4, 2017 1:34 PM
  • is there any way to solve my query..??

    Nabeel 


    Saturday, February 4, 2017 1:58 PM
  • Hi,

    I think you can create a VBA method to do this.

    Sub Test()
    Dim usedRow1 As Integer, usedRow2 As Integer
    Dim K, M
    M = 2
    usedRow1 = Sheets("Sheet1").UsedRange.Rows.Count
    usedRow2 = Sheets("Sheet2").UsedRange.Rows.Count
    
    'clear original data
    For i = 2 To usedRow1
    Sheets("Sheet1").Rows(i).ClearContents
    Next
    
    'build new data
    For i = 2 To usedRow2
       K = Sheets("Sheet2").Cells(i, "n").Value   'repeat times
       For j = 1 To K
       Sheets("Sheet1").Cells(M, "A") = Sheets("Sheet2").Cells(i, "A").Value
       Sheets("Sheet1").Cells(M, "B") = Sheets("Sheet2").Cells(i, "B").Value
       Sheets("Sheet1").Cells(M, "C") = ""
       Sheets("Sheet1").Cells(M, "D") = ""
       Sheets("Sheet1").Cells(M, "E") = Sheets("Sheet2").Cells(i, "C").Value
       Sheets("Sheet1").Cells(M, "F") = "G"
       Sheets("Sheet1").Cells(M, "G") = Sheets("Sheet2").Cells(i, "D").Value
       Sheets("Sheet1").Cells(M, "H") = "POST"
       Sheets("Sheet1").Cells(M, "I") = ""
       Sheets("Sheet1").Cells(M, "J") = Sheets("Sheet2").Cells(i, "E").Value
       Sheets("Sheet1").Cells(M, "K") = ""
       Sheets("Sheet1").Cells(M, "L") = Sheets("Sheet2").Cells(i, "F").Value
       Sheets("Sheet1").Cells(M, "M") = "CUST"
       Sheets("Sheet1").Cells(M, "N") = Sheets("Sheet2").Cells(i, "G").Value
       Sheets("Sheet1").Cells(M, "O") = "3245294100"
       Sheets("Sheet1").Cells(M, "P") = Sheets("Sheet2").Cells(i, "H").Value
       Sheets("Sheet1").Cells(M, "Q") = "POST"
       Sheets("Sheet1").Cells(M, "R") = Sheets("Sheet2").Cells(i, "I").Value
       Sheets("Sheet1").Cells(M, "S") = Sheets("Sheet2").Cells(i, "J").Value
       Sheets("Sheet1").Cells(M, "T") = ""
       Sheets("Sheet1").Cells(M, "U") = ""
       Sheets("Sheet1").Cells(M, "V") = ""
       Sheets("Sheet1").Cells(M, "W") = Sheets("Sheet2").Cells(i, "J").Value
       Sheets("Sheet1").Cells(M, "X") = Sheets("Sheet2").Cells(i, "K").Value
       Sheets("Sheet1").Cells(M, "Y") = Sheets("Sheet2").Cells(i, "K").Value
       Sheets("Sheet1").Cells(M, "Z") = "CNT"
       Sheets("Sheet1").Cells(M, "AA") = "50001"
       Sheets("Sheet1").Cells(M, "AB") = "C"
       Sheets("Sheet1").Cells(M, "AC") = Sheets("Sheet2").Cells(i, "M").Value
       M = M + 1
    Next
    Next
    End Sub
    
    
    Everytime you call this method,it will refresh sheet1 to get result you want.
    You can modify the code with your own actual situation.

    Hope that can help you

    Monday, February 6, 2017 5:45 AM
  • Dear terry 

    thank for your kind help & valueable time, this is gud as per my need, just make to upper case in all cell plz

    Nabeel Gondal

    Monday, February 6, 2017 4:23 PM
  • Dear Nabeel

    You can call UCase method to convert the date like this

    Sheets("Sheet1").Cells(M, "S") = UCase(Sheets("Sheet2").Cells(i, "J").Value)
       Sheets("Sheet1").Cells(M, "W") = UCase(Sheets("Sheet2").Cells(i, "J").Value)

    • Marked as answer by Nabeel Gondal Tuesday, February 7, 2017 5:08 AM
    Tuesday, February 7, 2017 1:24 AM
  • Dear terry 

    Job done..!!

    thanks for help, now this is great and easy to change for me in as per my requirment

    Nabeel Gondal

    Tuesday, February 7, 2017 5:08 AM