Macro to copy data based on specific criteria

howard

Well-known Member
Joined
Jun 26, 2006
Messages
6,595
Office Version
  1. 2021
Platform
  1. Windows
I have the following text and values in E1 to F3

MRTB Bal owing -209562
CMTRQ Bal owing -368012
SRQE Bal owing -30171.3




Where the text to the left in E1 to E3 is the same as Col E from row 8 onwards and col D contains Bal B/FWD, then copy the value in Col F where the text to the left is the same as the text in Col E from row 8 onwards and paste as special values in Col G with the minus sign to removed in the same row as the criteria appears

For eg if BRTB and Bal B/FWD are in row 8 , then paste the value with the minus sign removed in G8


I have tried to write code to do this but only zeroes are being pasted

Code:
 Sub CopyBalances_YearEnd()
    Dim ws As Worksheet
    Dim LastRow As Long
    Dim i As Long
    
    ' Set the worksheet you want to work with (Change "Details" to your sheet's name)
    Set ws = ThisWorkbook.Sheets("Details")
    
    ' Find the last row in the worksheet
    LastRow = ws.Cells(ws.Rows.Count, "E").End(xlUp).Row
    
    ' Loop through each row in the worksheet
    For i = 8 To LastRow ' Assuming data starts from row 8, change if needed
        If InStr(1, ws.Cells(i, "E").Value, "MRTB") > 0 Then
            If InStr(1, ws.Cells(i, "D").Value, "Bal B/FWD") > 0 Then
                ' Copy balance from column F, remove '-' if present, and paste as a value
                ws.Cells(i, "F").Value = Abs(ws.Cells(i, "F").Value)
                ws.Cells(i, "F").Copy
                ws.Cells(i, "G").PasteSpecial Paste:=xlPasteValues
            End If
        ElseIf InStr(1, ws.Cells(i, "E").Value, "CMTRQ") > 0 Then
            If InStr(1, ws.Cells(i, "D").Value, "Bal B/FWD") > 0 Then
                ' Copy balance from column F, remove '-' if present, and paste as a value
                ws.Cells(i, "F").Value = Abs(ws.Cells(i, "F").Value)
                ws.Cells(i, "F").Copy
                ws.Cells(i, "G").PasteSpecial Paste:=xlPasteValues
            End If
        ElseIf InStr(1, ws.Cells(i, "E").Value, "SRQE") > 0 Then
            If InStr(1, ws.Cells(i, "D").Value, "Bal B/FWD") > 0 Then
                ' Copy balance from column F, remove '-' if present, and paste as a value
                ws.Cells(i, "F").Value = Abs(ws.Cells(i, "F").Value)
                ws.Cells(i, "F").Copy
                ws.Cells(i, "G").PasteSpecial Paste:=xlPasteValues
            End If
        End If
    Next i
End Sub


Your assistance in this regard is most appreciated
 

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple
Try to attach a sample with XL2BB (or sharing file via gg drive)
 
Upvote 0
Please find link to sample data

I have shown what is required on sheet "After'

 
Upvote 0
Below code does exactly as per sheet After.
(Though I do not understand why the new output value of columm G are exact old balance in F1:F3! )
VBA Code:
Option Explicit
Sub CopyBalance_YearEnd()
Dim lr&, i&, j&, Data, Amount, list, id As String
With Sheets("Details")
    list = .Range("E1:F5").Value  ' criteria range
    lr = .Cells(.Rows.Count, "E").End(xlUp).Row ' last used row
    Data = .Range("D8:E" & lr).Value ' range with criteria data
    Amount = .Range("F8:G" & lr).Value ' range with amount data, need to be changed
    For i = 1 To UBound(Data)
        id = Data(i, 2) & " " & Left(Data(i, 1), 3) & " owing" ' generate string, i.e, "MRTB Bal owing" or "MRTB 100 owing"
        For j = 1 To UBound(list)
            If list(j, 1) = id Then ' if id string exists in criteria range, i.e, "MRTB Bal owing":
                Amount(i, 2) = -list(j, 2) ' replace column G with - F1:F3
                Amount(i, 1) = 0 ' column F = 0
            End If
        Next
    Next
    .Range("F8").Resize(UBound(Amount), 2).Value = Amount
End With
End Sub
 
Upvote 0
Solution
Many thanks for the help. The balance of the rows where Bal B/FWD is in Col D is deleted when starting the new financial year
 
Upvote 0

Forum statistics

Threads
1,223,911
Messages
6,175,334
Members
452,636
Latest member
laura12345

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top