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
Your assistance in this regard is most appreciated
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