Match, cut & Paste

[puru]

<TABLE style="WIDTH: 213pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=284 border=0 x:str><COLGROUP><COL style="WIDTH: 112pt; mso-width-source: userset; mso-width-alt: 5449" width=149><COL style="WIDTH: 101pt; mso-width-source: userset; mso-width-alt: 4937" width=135><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD class=xl34 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-BOTTOM-COLOR: #ece9d8; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 112pt; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=149 height=17 x:num="0">CAD - </TD><TD class=xl34 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT-COLOR: windowtext; BORDER-BOTTOM-COLOR: #ece9d8; WIDTH: 101pt; BACKGROUND-COLOR: transparent" width=135 x:num="-2450000">CAD (2,450,000.00)</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl34 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-BOTTOM-COLOR: #ece9d8; BORDER-LEFT: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17 x:num="2450000">CAD 2,450,000.00 </TD><TD class=xl34 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT-COLOR: windowtext; BORDER-BOTTOM-COLOR: #ece9d8; BACKGROUND-COLOR: transparent" x:num="0">CAD - </TD></TR></TBODY></TABLE>



<TABLE style="WIDTH: 213pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=284 border=0 x:str><COLGROUP><COL style="WIDTH: 112pt; mso-width-source: userset; mso-width-alt: 5449" width=149><COL style="WIDTH: 101pt; mso-width-source: userset; mso-width-alt: 4937" width=135><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD class=xl34 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-BOTTOM-COLOR: #ece9d8; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 112pt; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=149 height=17 x:num="2138248.62">CAD 2,138,248.62 </TD><TD class=xl34 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT-COLOR: windowtext; BORDER-BOTTOM-COLOR: #ece9d8; WIDTH: 101pt; BACKGROUND-COLOR: transparent" width=135 x:num="0">CAD - </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl34 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-BOTTOM-COLOR: #ece9d8; BORDER-LEFT: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17 x:num="0">CAD - </TD><TD class=xl34 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT-COLOR: windowtext; BORDER-BOTTOM-COLOR: #ece9d8; BACKGROUND-COLOR: transparent" x:num="-2138248.62">CAD (2,138,248.62)</TD></TR></TBODY></TABLE>



<TABLE style="WIDTH: 213pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=284 border=0 x:str><COLGROUP><COL style="WIDTH: 112pt; mso-width-source: userset; mso-width-alt: 5449" width=149><COL style="WIDTH: 101pt; mso-width-source: userset; mso-width-alt: 4937" width=135><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD class=xl34 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-BOTTOM-COLOR: #ece9d8; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 112pt; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=149 height=17 x:num="0">CAD - </TD><TD class=xl34 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT-COLOR: windowtext; BORDER-BOTTOM-COLOR: #ece9d8; WIDTH: 101pt; BACKGROUND-COLOR: transparent" width=135 x:num="-1899000">CAD (1,899,000.00)</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl34 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-BOTTOM-COLOR: #ece9d8; BORDER-LEFT: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17 x:num="1899000">CAD 1,899,000.00 </TD><TD class=xl34 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT-COLOR: windowtext; BORDER-BOTTOM-COLOR: #ece9d8; BACKGROUND-COLOR: transparent" x:num="0">CAD - </TD></TR></TBODY></TABLE>


I have debits in Column F and Credits in Column G
Now what I want is
1) A VB Code that will automatecally match two amounts in column F & G based on the negative and positive amount. These amounts could be in F45 the match in G195 or F58 & match in G88. Please be advised that the columns are not always F & G.
2) After amounts in two columns are matched a VB Code that will cut matched rows from "sheet1" and paste to "sheet2" and delete the blank rows from "sheet1"

 

[ColinKJ]

Hi puru,

I see no one has responded to your post and I noticed it the other day.

Do you still need assistance ?

The basis of what you want is straight forward, but the confusing bit is when you say :

"Please be advised that the columns are not always F & G."

What other columns would they be in. Is there only one value on each Row? either positive or negative.?

Regards

ColinKJ

 

[puru]

Hi puru,

I see no one has responded to your post and I noticed it the other day.

Do you still need assistance ?

The basis of what you want is straight forward, but the confusing bit is when you say :

"Please be advised that the columns are not always F & G."

What other columns would they be in. Is there only one value on each Row? either positive or negative.?

Regards

ColinKJ

Hello Colin,

The columns are not always F&G means the colums could any one of these (D&E, F&G, H&I, J&K, L&M and N&O).
And yes you are right. There is only one value on each row (positive or negative).

Thanks,

Puru

 

[ColinKJ]

Hi puru,

I´ll get back to you on this.

ColinKJ

 

[ColinKJ]

Hi puru,

OK, I've put something together for you.

Don`t know how familiar you are with VBA code, but just put this in a standard module, and assign it to a Ctrl+key combination.

Sub CutAndPaste()
Application.ScreenUpdating = False
For Col = 4 To 14 Step 2
d:
    Ro = ActiveCell.SpecialCells(xlLastCell).Row
    For R = 1 To Ro
        If Cells(R, Col) <> "" And Len(Cells(R, Col).Text) > 6 Then
            x = Cells(R, Col).Text: Valu = 0: ValTX = ""
            For w = 1 To Len(x)
                Y = Mid$(x, w, 1)
                Select Case Y
                    Case IsNumeric(Y), ".", "0"
                    ValTX = ValTX + Y
                    Case "("
                    ValTX = "-" + ValTX
                End Select
            Next w
            Valu = Val(ValTX)
 
            For RR = 1 To Ro
                If Cells(RR, Col + 1) <> "" And Len(Cells(RR, Col + 1).Text) > 6 Then
                    x = Cells(RR, Col + 1).Text: Valu1 = 0: ValTX1 = ""
                    For w = 1 To Len(x)
                        Y = Mid$(x, w, 1)
                        Select Case Y
                            Case IsNumeric(Y), ".", "0"
                            ValTX1 = ValTX1 + Y
                            Case "("
                            'ValTX1 = "-" + ValTX1
                        End Select
                    Next w
                    Valu1 = Val(ValTX1)
                    If Valu1 = Valu Then
                        Sheets(2).Activate
                        Ro2 = ActiveCell.SpecialCells(xlLastCell).Row + 1
                        Sheets(2).Cells(Ro2, 1) = Sheets(1).Cells(R, Col).Text
                        Sheets(2).Cells(Ro2, 2) = Sheets(1).Cells(R, Col + 1).Text
                        Sheets(2).Cells(Ro2 + 1, 1) = Sheets(1).Cells(RR, Col).Text
                        Sheets(2).Cells(Ro2 + 1, 2) = Sheets(1).Cells(RR, Col + 1).Text
                        Sheets(1).Activate
                        If R > RR Then
                            Sheets(1).Rows(Mid$(Str$(R), 2) + ":" + Mid$(Str$(R), 2)).Delete
                            Sheets(1).Rows(Mid$(Str$(RR), 2) + ":" + Mid$(Str$(RR), 2)).Delete
                        Else
                            Sheets(1).Rows(Mid$(Str$(RR), 2) + ":" + Mid$(Str$(RR), 2)).Delete
                            Sheets(1).Rows(Mid$(Str$(R), 2) + ":" + Mid$(Str$(R), 2)).Delete
                        End If
                        GoTo d
                    End If
                End If
            Next RR
        End If
    Next R
Next Col
Application.ScreenUpdating = True
End Sub

I've used the examples you posted plus additional values scattered across the columns (D&E, F&G, H&I, J&K, L&M and N&O) and various Rows on Sheet(1).

The matched results are copied into Columns A & B on Sheet(2).

If you want a copy of the test WB, send me a private message with an email address, and I'll send it over.

Regards

ColinKJ