none
VBA code for find text and replace its value RRS feed

  • Question

  • Assume below data present in worksheet 

     Value    Unit   ---> These two are my column in worksheet at any postion

     NA       KLOC                            after running vba my data will look like ---->     Value         Unit

    0.98     kLOC                                                                                                     NA          KLOC , FP

    0.9       OTH                                                                                             0.9*8= 7.2        LOC/PD

    03        FP                                                                                                0.3*8 =2.4        FP/PD

    NA       FP

    If unit column contain  KLOC,   FP then I rename this as KLOC =LOC/PD and FP=FP/PD and theire new value= value *0.8

    For NA value don't  change anything
    Wednesday, February 21, 2018 4:16 AM

Answers

  • Hi,

    I'm not sure if I can fully understand your requirement. I'm afraid you need to provide all conditions.
    The below sample is based on my guess.
        

    *** VBA code in [Replace] button ***
    ' ---[Replace] button
    Private Sub btn_Replace_Click()
        Dim lastRow As Long
        lastRow = Cells(Rows.Count, 1).End(xlUp).Row
        Dim myRow As Long
        For myRow = 2 To lastRow
            If (Cells(myRow, 1).Value = "NA") Then
                Cells(myRow, 4).Value = Cells(myRow, 1).Value
                Cells(myRow, 5).Value = Cells(myRow, 2).Value
            Else
                Select Case Cells(myRow, 2).Value
                    Case Is = "KLOC"
                        Cells(myRow, 4).Value = Cells(myRow, 1).Value * 0.8
                        Cells(myRow, 5).Value = "LOC/PD"
                    Case Is = "FP"
                        Cells(myRow, 4).Value = Cells(myRow, 1).Value * 0.8
                        Cells(myRow, 5).Value = "FPC/PD"
                    Case Else
                        Cells(myRow, 4).Value = Cells(myRow, 1).Value
                        Cells(myRow, 5).Value = Cells(myRow, 2).Value
                End Select
            End If
        Next
    End Sub
    
    [note]
      I dare to put replaced values in different columns, i.e. D[4] and E[5], instead in original A[1] and B[2].
      If you use this code, please modify column number from 4 to 1, from 5 to 2.

    Regards.

    Ashidacchi

    Wednesday, February 21, 2018 5:37 AM

All replies

  • Hi,

    Sorry, a text you provided looks somewhat confusing. Could you insert a screenshot or input that text in Code Block.
       This is Code Block...

    Regards,

    Ashidacchi

    Wednesday, February 21, 2018 4:56 AM
  • Hi,

    I'm not sure if I can fully understand your requirement. I'm afraid you need to provide all conditions.
    The below sample is based on my guess.
        

    *** VBA code in [Replace] button ***
    ' ---[Replace] button
    Private Sub btn_Replace_Click()
        Dim lastRow As Long
        lastRow = Cells(Rows.Count, 1).End(xlUp).Row
        Dim myRow As Long
        For myRow = 2 To lastRow
            If (Cells(myRow, 1).Value = "NA") Then
                Cells(myRow, 4).Value = Cells(myRow, 1).Value
                Cells(myRow, 5).Value = Cells(myRow, 2).Value
            Else
                Select Case Cells(myRow, 2).Value
                    Case Is = "KLOC"
                        Cells(myRow, 4).Value = Cells(myRow, 1).Value * 0.8
                        Cells(myRow, 5).Value = "LOC/PD"
                    Case Is = "FP"
                        Cells(myRow, 4).Value = Cells(myRow, 1).Value * 0.8
                        Cells(myRow, 5).Value = "FPC/PD"
                    Case Else
                        Cells(myRow, 4).Value = Cells(myRow, 1).Value
                        Cells(myRow, 5).Value = Cells(myRow, 2).Value
                End Select
            End If
        Next
    End Sub
    
    [note]
      I dare to put replaced values in different columns, i.e. D[4] and E[5], instead in original A[1] and B[2].
      If you use this code, please modify column number from 4 to 1, from 5 to 2.

    Regards.

    Ashidacchi

    Wednesday, February 21, 2018 5:37 AM
  • 

    This is one of the page of my Dashboard

    I have modified your code as below

              

    Set pDashboard = pDashboardWB.Sheets("Accounts")

     Dim lastRow As Long
        lastRow = pDashboard.Cells(7, 9).End(xlUp).Row
        Dim myRow As Long

        MsgBox "Entered"

        For myRow = 7 To lastRow
            If (pDashboard.Cells(myRow, 9).Value = "NA") Then
                pDashboard.Cells(myRow, 9).Value = pDashboard.Cells(myRow, 9).Value
                pDashboard.Cells(myRow, 10).Value = pDashboard.Cells(myRow, 10).Value
            Else
                Select Case pDashboard.Cells(myRow, 10).Value
                    Case Is = "KLOC"
                        pDashboard.Cells(myRow, 9).Value = pDashboard.Cells(myRow, 9).Value * 8
                        pDashboard.Cells(myRow, 10).Value = "LOC/PD"
                    Case Is = "FP"
                        pDashboard.Cells(myRow, 9).Value = pDashboard.Cells(myRow, 9).Value * 8
                        pDashboard.Cells(myRow, 10).Value = "FPC/PD"
                    Case Else
                        pDashboard.Cells(myRow, 9).Value = pDashboard.Cells(myRow, 9).Value
                        pDashboard.Cells(myRow, 10).Value = pDashboard.Cells(myRow, 10).Value
                End Select
            End If
        Next

    But its not working.  Please provide your inputs.

    Thursday, February 22, 2018 4:36 AM
  • Please share your file via cloud storage (OneDrive, Dropbox, and so on).
    I use my time for you, so, please use your time for me to make code for you.

    Ashidacchi

    Thursday, February 22, 2018 4:39 AM
  • Did you check the value of lastRow?
    add one line after astRow = pDashboard.Cells(7, 9).End(xlUp).Row
    Msgbox "lastRow=" & lastRow

    And you need to explain the further detail about "its not working". 

    Ashidacchi

    Thursday, February 22, 2018 4:47 AM