Change post back every row to post back once

[capson]

Hello,

I have Code that finds and replaces values in a comma delimited string from a list on a separate sheet

Ex
cell value = aa,bb ,cc ,dd , ee

Replace values
aa
bb = bob
cc = joe
dd
ee = frank

I get
cell value = aa, bob, joe, dd, frank

Code works fine but is slow if I have large dataset

It postback on every row, I want to post back only once

I tried changing

Next n
        wsFV.Range("A" & I).value = Mid(temp(I - 1), 3)
    Next I
End Sub

Dim temp2 As Variant
ReDim temp2(1 To sLR - 1)
Next n
        temp2(I - 1) = Mid(temp(I - 1), 3)
    Next I
    wsFV.Range("A2:A" & sLR) = temp2
End Sub

I get the proper values for temp2(I - 1) but the output gives only the last found replacement value

so if Joe was the last found replacement value the posted pack data looks like

Joe
Joe
Joe
.
.
.
Joe

Thanks

Sub eFindReplaceSPLIT_Orgs()
    Dim FindValues As Variant, Replaceval As Variant, G As Variant, temp() As Variant, temp2 As Variant
    Dim wsFV As Worksheet, wsRV As Worksheet
    Dim sLR As Long, tLR As Long, I As Long, j As Long, n As Long, counter As Long


    Set wsFV = ThisWorkbook.Worksheets("AA")
    Set wsRV = ThisWorkbook.Worksheets("ZZ")


    sLR = wsFV.Range("A" & wsFV.Rows.count).End(xlUp).Row
    tLR = wsRV.Range("A" & wsRV.Rows.count).End(xlUp).Row


    ReDim temp(1 To sLR - 1)
    ReDim temp2(1 To sLR - 1)
    For I = 2 To sLR
        counter = 1
        G = Split(wsFV.Cells(I, 1).value, ", ")
        temp(I - 1) = wsFV.Cells(I, 1).value
        For n = LBound(G) To UBound(G)
            For j = 2 To tLR
                If splitCell(wsFV.Range("A" & I), ", ", n) = wsRV.Cells(j, "A") Then
                   Replaceval = IIf(wsRV.Cells(j, "B").value <> vbNullString, wsRV.Cells(j, "B").value, wsRV.Cells(j, "A").value)
                   temp(I - 1) = IIf(counter = 1, ", " & Replaceval, temp(I - 1) & ", " & Replaceval)
                   'Debug.Print temp(i - 1)
                   counter = counter + 1
                End If
            Next j
        Next n
        wsFV.Range("A" & I).value = Mid(temp(I - 1), 3)
    Next I
End Sub

 

[Joe4]

It postback on every row, I want to post back only once

Try placing the following at the very beginning of your code:

    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual

and this at the very end:

    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True

and see if that helps speed things up.

 

[capson]

Hello Joe,

when I run the code it is in a series with other macros an I have those already at the beginning of the process

Ex:

Sub RunAll()
FastWB True

Call This
Call That
Call [COLOR=#333333]eFindReplaceSPLIT_Orgs[/COLOR]

FastWB False
End Sub

Public Sub FastWB(Optional ByVal opt As Boolean = True)
    With Application
        .Calculation = IIf(opt, xlCalculationManual, xlCalculationAutomatic)
        .DisplayAlerts = Not opt
        .EnableAnimations = Not opt
        .EnableEvents = Not opt
        .ScreenUpdating = Not opt
        .CutCopyMode = Not opt
        .Cursor = xlWait
    End With
    FastWS , opt
End Sub


Public Sub FastWS(Optional ByVal ws As Worksheet = Nothing, _
                  Optional ByVal opt As Boolean = True)
    If ws Is Nothing Then
        For Each ws In Application.ActiveWorkbook.Sheets
            EnableWS ws, opt
        Next
    Else
        EnableWS ws, opt
    End If
End Sub


Private Sub EnableWS(ByVal ws As Worksheet, ByVal opt As Boolean)
    With ws
        .DisplayPageBreaks = False
        .EnableCalculation = Not opt
        .EnableFormatConditionsCalculation = Not opt
        .EnablePivotTable = Not opt
        .Application.Cursor = xlDefault
    End With
End Sub

 

[Fluff]

How about something like this

Sub Rep()
   Dim i As Long
   Dim ary As Variant
   Dim wsFV As Worksheet, wsRV As Worksheet
   
   Set wsFV = ThisWorkbook.Worksheets("AA")
   Set wsRV = ThisWorkbook.Worksheets("ZZ")
Application.ScreenUpdating = False
   ary = wsRV.Range("A2", wsRV.Range("B" & Rows.Count).End(xlUp))
   For i = 1 To UBound(ary, 1)
      wsFV.Range("A:A").Replace ary(i, 1), ary(i, 2), xlPart, , False, , False
   Next i
End Sub

 

[Joe4]

Without knowing what each of those other procedures calls, I can't say that I "trust it" to filter all the way down to that procedure.
In any event, it doesn't hurt to place it in that procedure also.
Why not try and see if it makes any difference?

 

[capson]

Hello, Fluff

I originally tried that approach, it gives incorrect output in real use situation

List     Replacment_List
DPH    Dept Public Health

If
cell value = Los Angeles Sherrif Dept, CDPH, APDIM

I get
cell value = Los Angeles Sherrif Dept, CDPH Housing for Health, APDIM

but CDPH should remain unchanged

 

[capson]

Joe, I just tried it with your approach but ran the same, no noticeable difference in time

 

[capson]

This did it

WorksheetFunction.Transpose(temp2)