Correction of the vba code

Sachin Singh
Sachin Singh used Ask the Experts™
on
https://www.experts-exchange.com/questions/29115581/Copy-and-paste-the-data-by-add-subtract-by-percentage.html
https://www.experts-exchange.com/questions/29115579/Conditionally-copy-and-paste-of-the-data-with-add-subtract-method.html
Plz have a look in this post
when i run the code  one after another  then they are deleting the previous data
Plz look into it and do needful
Sub STEP8_2()
Dim ws2 As Worksheet, ws3 As Worksheet, ws4 As Worksheet
Dim x2, x3, x4, arrG(), dict
Dim i As Long, lr As Long

Application.ScreenUpdating = False

Set ws2 = Sheets("Sheet2")
Set ws3 = Sheets("Sheet3")
Set ws4 = Sheets("Sheet4")
lr = ws4.Cells(Rows.Count, 1).End(xlUp).Row
x2 = ws2.Range("A1").CurrentRegion.Value
x3 = ws3.Range("A1").CurrentRegion.Value
x4 = ws4.Range("A1:T" & lr).Value
ReDim arrG(1 To UBound(x4, 1), 1 To 1)

Set dict = CreateObject("Scripting.Dictionary")

For i = 2 To UBound(x2, 1)
    dict.Item(x2(i, 2)) = x2(i, 13) & "_" & Round(x2(i, 4) * 1.005, 2)
Next i

For i = 2 To UBound(x3, 1)
    dict.Item(x3(i, 2)) = x3(i, 13) & "_" & Round(x3(i, 4) * (1 - 0.005), 2)
Next i

For i = 1 To UBound(x4, 1)
    If dict.exists(x4(i, 3)) Then
        If x4(i, 10) = Split(dict.Item(x4(i, 3)), "_")(0) Then
            arrG(i, 1) = Split(dict.Item(x4(i, 3)), "_")(1)
        End If
    End If
Next i
ws4.Range("G1").Resize(UBound(x4, 1), 1).Value = arrG
Application.ScreenUpdating = True
End Sub

Open in new window


Sub STEP8()
Dim ws2 As Worksheet, ws3 As Worksheet, ws4 As Worksheet
Dim x2, x3, x4, arrG(), arrL(), dict
Dim i As Long, lr As Long

Application.ScreenUpdating = False

Set ws2 = Sheets("Sheet2")
Set ws3 = Sheets("Sheet3")
Set ws4 = Sheets("Sheet4")
lr = ws4.Cells(Rows.Count, 1).End(xlUp).Row
x2 = ws2.Range("A1").CurrentRegion.Value
x3 = ws3.Range("A1").CurrentRegion.Value
x4 = ws4.Range("A1:T" & lr).Value
ReDim arrG(1 To UBound(x4, 1), 1 To 1)
ReDim arrL(1 To UBound(x4, 1), 1 To 1)

Set dict = CreateObject("Scripting.Dictionary")

For i = 2 To UBound(x2, 1)
    dict.Item(x2(i, 2)) = x2(i, 13) & "_" & x2(i, 4) & "_" & x2(i, 4) - 0.05
Next i

For i = 2 To UBound(x3, 1)
    dict.Item(x3(i, 2)) = x3(i, 13) & "_" & x3(i, 4) & "_" & x3(i, 4) + 0.05
Next i

For i = 1 To UBound(x4, 1)
    If dict.exists(x4(i, 3)) Then
        If x4(i, 10) <> Split(dict.Item(x4(i, 3)), "_")(0) Then
            arrL(i, 1) = Split(dict.Item(x4(i, 3)), "_")(1)
            arrG(i, 1) = Split(dict.Item(x4(i, 3)), "_")(2)
        End If
    End If
Next i
ws4.Range("G1").Resize(UBound(x4, 1), 1).Value = arrG
ws4.Range("L1").Resize(UBound(x4, 1), 1).Value = arrL
Application.ScreenUpdating = True
End Sub

Open in new window




First code is perfect for getting  percentage  No issues with the first code
Second code is creating issues second code is  removing the data from column G and L column
I already have data in column G and L and i will run the code  & it should not  affect any data in any column and in column G and column L while doing the process
So it is my request plz look into it
TwoClick.xlsb
TwoClick.xlsb
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Subodh Tiwari (Neeraj)Excel & VBA Expert
Most Valuable Expert 2018
Awarded 2015

Commented:
So will you always run the macro STEP8_2 first to place the percentage values in column G and then run the macro Step8 to place the amounts in column G and column L but you want to retain any existing values in column G if any. Right?
Subodh Tiwari (Neeraj)Excel & VBA Expert
Most Valuable Expert 2018
Awarded 2015

Commented:
Also is it not better to call both these macros from another macro in sequence like below?

Sub PopulateColumnGAndL()
    Call STEP8_Percentage
    Call STEP8
End Sub

Open in new window

So that when macro PopulateColumnGAndL is run, it will call STEP8_Percentage macro first and then call the macro STEP8.

Author

Commented:
Neeraj Sir first i will run percentage code and then second one
And while doing all the process we don't have to change any data  affect any data and we have to complete the process
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Author

Commented:
Yes Neeraj Sir i want to retain that data
Subodh Tiwari (Neeraj)Excel & VBA Expert
Most Valuable Expert 2018
Awarded 2015

Commented:
So what about the second option I gave you i.e. run both the code in sequence by clicking the one button only?

Author

Commented:
Neeraj Sir but the second code is creating issue it is removing the data in column G and L
We have to edit the code so that it can't affect the data
Subodh Tiwari (Neeraj)Excel & VBA Expert
Most Valuable Expert 2018
Awarded 2015

Commented:
I tweaked the code STEP8 like below...

Sub STEP8()
Dim ws2 As Worksheet, ws3 As Worksheet, ws4 As Worksheet
Dim x2, x3, x4, arrG(), arrL(), dict
Dim i As Long, lr As Long

Application.ScreenUpdating = False

Set ws2 = Sheets("Sheet2")
Set ws3 = Sheets("Sheet3")
Set ws4 = Sheets("Sheet4")
lr = ws4.Cells(Rows.Count, 1).End(xlUp).Row
x2 = ws2.Range("A1").CurrentRegion.Value
x3 = ws3.Range("A1").CurrentRegion.Value
x4 = ws4.Range("A1:T" & lr).Value
ReDim arrG(1 To UBound(x4, 1), 1 To 1)
ReDim arrL(1 To UBound(x4, 1), 1 To 1)

Set dict = CreateObject("Scripting.Dictionary")

For i = 2 To UBound(x2, 1)
    dict.Item(x2(i, 2)) = x2(i, 13) & "_" & x2(i, 4) & "_" & x2(i, 4) - 0.05
Next i

For i = 2 To UBound(x3, 1)
    dict.Item(x3(i, 2)) = x3(i, 13) & "_" & x3(i, 4) & "_" & x3(i, 4) + 0.05
Next i

For i = 1 To UBound(x4, 1)
    If x4(i, 7) <> "" Then
        arrG(i, 1) = x4(i, 7)
    Else
        If dict.exists(x4(i, 3)) Then
            If x4(i, 10) <> Split(dict.Item(x4(i, 3)), "_")(0) Then
                arrL(i, 1) = Split(dict.Item(x4(i, 3)), "_")(1)
                arrG(i, 1) = Split(dict.Item(x4(i, 3)), "_")(2)
            End If
        End If
    End If
Next i
ws4.Range("G1").Resize(UBound(x4, 1), 1).Value = arrG
ws4.Range("L1").Resize(UBound(x4, 1), 1).Value = arrL
Application.ScreenUpdating = True
End Sub

Open in new window


In the attached, you may click the button called "Populate Column G & L With Percentage and Amount" to place both the percentage and the amount in column G and L.

If you wish you can still run both the macros individually. Both the macros are placed on Module1.
TwoClick-v4.xlsb

Author

Commented:
Now i understood u r missing this info
plz see my 2nd sample file that i have attached in that file in sheet4 plz see column L and there is already data in Column L NA
so we dont have to remove that NA  i want that also
Excel & VBA Expert
Most Valuable Expert 2018
Awarded 2015
Commented:
Okay, let me know if the attached works for you.
TwoClick-v4.xlsb

Author

Commented:
Ur r Right Neeraj Sir Now it's perfect
Thnx Neeraj Sir for ur great support
But i need info if i have to increase or decrease the percentage  what correction i have to made so in future if change is required i will do the changes
Subodh Tiwari (Neeraj)Excel & VBA Expert
Most Valuable Expert 2018
Awarded 2015

Commented:
In the first code you posted in your description, line#20 and 24 calculates the percentage values.

dict.Item(x2(i, 2)) = x2(i, 13) & "_" & Round(x2(i, 4) * 1.005, 2)

dict.Item(x3(i, 2)) = x3(i, 13) & "_" & Round(x3(i, 4) * (1 - 0.005), 2)

Where 0.5% is equivalent to .005.

Author

Commented:
Thnx Neeraj Sir for the info
Have a Great Day
Subodh Tiwari (Neeraj)Excel & VBA Expert
Most Valuable Expert 2018
Awarded 2015

Commented:
Thanks & you too!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial