Posts by Mumps

    First of all, I didn't realize that you were working with two files, one containing the macro and the other the data. The original macro I suggested won't work properly because it refers to the workbook containing the macro, not the data workbook. So unless you have changed the macro, it won't work as written. Secondly, do you want the identification of the 4 columns and the further calculations to be done when you click the "Calculate" button on the userform? Also, what further calculations do you want to do?

    It's hard to work with pictures. Please attach a file that includes all your sheets, userforms and macros. Explain in detail what you want to do referring to specific cells, rows, columns and sheets using a few examples from your data (de-sensitized if necessary).

    You are very welcome. :)

    This should also work:

    1. Sub PasteValues()
    2. Application.ScreenUpdating = False
    3. Dim fnd As Range
    4. Set fnd = Rows(11).Find("ok", LookIn:=xlValues, lookat:=xlWhole)
    5. If Not fnd Is Nothing Then
    6. Range(Cells(50, fnd.Column), Cells(586, fnd.Column)).Value = Range(Cells(50, fnd.Column), Cells(586, fnd.Column)).Value
    7. End If
    8. Application.CutCopyMode = False
    9. Application.ScreenUpdating = True
    10. End Sub


    The problem was caused by the merged cells containing the dates. You should avoid using merged cells because they almost always cause problems for macros. I have unmerged the cells. Try the attached file.


    • mithu.xlsm

      (21.47 kB, downloaded 5 times, last: )


    Code for UserForm2

    Open the attached file. The macro below is in Module1. Change the workbook name in the code to suit your needs. Make sure that both files are open, select any cell in row 2 in the JWTESTLIST file and click the "Copy Data" button.

    You also have red cells in row 20. Do you want to check only column L for red cells? Could column L have more than one red cell? The file you posted doesn't include your userform. Please post a copy of your file which includes the userform and any macros you are currently using.