How to cycle through each row in a column by moving each row individually to another column!

gint3232 · Apr 4, 2018

Hi everyone,
I am wanting to cycle through each row in a column via VBA by moving each row individually to another column

And then put back where if evaluation = false, if not keep Going (looping)

I have kindly been given Vba that compares columns A with B, but firstly before running this vba

I need a way of moving each row from two columns to another column hopefully witihin the same worksheet.

Then I’ll run the VBA, if the vba comes up with False it’ll keep going till it evaluates to true,

I wish to try every permutation of evaluating for matches the = true

For example in English I wish …

to start by Moving cells C1+D1 to A1 B1 and also C2+D2 to A2 B2
then run vba to evaluate

If evaluation if = false, keep Going (looping) then

Put the moved (C2+D2 to A2 B2) back to its original location C2+D2 and then get and move the next rows Values over

If evaluation if = false, keep Going (by doing the same) then

Same again

If and when we get to the end of this loop then

start at the 1^st row again and this time move 2 rows over this time and run the evaluating vba

If and when we get to the end of this loop

Start again and move at the 1^st row again this time move the next 3 rows over and evaluate

If and when we get to the end of this loop

Start again and move at the 1^st row again this time move the next 4 rows over and evaluate

and so on and so on till the end of this loop

When finished doing this

start at the beginning again this time skip the second row and start a the third row
When finished doing this

start at the beginning again this time skip the second row and the third row and start at the 4th row

and so on and so on till the end of this loop

by the end of these nested loops we'll have tried all possible combinations for any matches- true

...all = true will be moved out and flagged somewhere else in the workbook
Else

if = true, flag as true and move to some other location in the worksheet

start looping through the records again

Move C2+D2 to A2 B2 and also C3+D3 to A3 B3

Run evaluation if = false, keep Going (looping)

if = true, flag as true and move to another location in the worksheet

start looping again (looping)

end if

loop

The code that evaluates is

Code:

Sub CompareCol()

   Dim Cl As Range
   Dim Cnt As Long
   
   With CreateObject("scripting.dictionary")
      For Each Cl In Range("A2", Range("A" & Rows.Count).End(xlUp))
         If Not .exists(Cl.Value) Then
            If WorksheetFunction.CountIf(Range("A:A"), Cl.Value) = WorksheetFunction.CountIf(Range("B:B"), Cl.Value) Then Cnt = Cnt + 1
            .Add Cl.Value, Nothing
         End If
      Next Cl
      If Cnt = .Count Then
         MsgBox True
      Else
         MsgBox False
      End If
   End With
End Sub

and was provided by Fluff at https://www.mrexcel.com/forum/excel...r-col-same-count-values-regardless-order.html

gint3232 · Apr 22, 2018

Ah! seems i found one thats to tough for this forum...surprise surprise!

gint3232 · Apr 22, 2018

well, baby steps right enough!! i moved the cnt = 2 and wallah!! hey presto!!!,,,thanks for nothing

How to cycle through each row in a column by moving each row individually to another column!

gint3232

New Member

Excel Facts

gint3232

New Member

gint3232

New Member

Similar threads

Forum statistics

Share this page

How to cycle through each row in a column by moving each row individually to another column!

gint3232

New Member

Excel Facts

gint3232

New Member

gint3232

New Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock