loop code require

[VBABEGINER]

Hi All,

I'm trying to write one logic (in loop). How can I write rk value. I want to read rk every cell value.

Sub code()

Sheets("Sheet1").Select

Dim cn As Integer
Dim i As Integer

i = 2
cn = Range("BN" & Rows.Count).End(xlUp).Row

Dim rk As Integer
rk = Range("BN2") 'comment - read every value..

For i = 2 To cn
If ActiveSheet.Range(Cells(i, "BN")) = rk Then


End If
Next i

End Sub

 

[Jeffrey Mahoney]

Please try this

Sub CheckRK()
  Dim Cel As Range
  Dim rk As Integer
  
  Sheets("Sheet1").Activate
  
  rk = Range("BN2").Value
  
  For Each Cel In Range(Range("BN2"), Range("BN" & Cells.Rows.Count).End(xlUp))
    If Cel.Value = rk Then
      'Do Something
      MsgBox Cel.Address
    End If
  Next Cel
  
End Sub

 

[VBABEGINER]

Please try this

Sub CheckRK()
  Dim Cel As Range
  Dim rk As Integer
 
  Sheets("Sheet1").Activate
 
  rk = Range("BN2").Value
 
  For Each Cel In Range(Range("BN2"), Range("BN" & Cells.Rows.Count).End(xlUp))
    If Cel.Value = rk Then
      'Do Something
      MsgBox Cel.Address
    End If
  Next Cel
 
End Sub

can you please help here after - If Cel.Value = rk Then

Sub checkRK()
Dim Cel As Range
Dim rk As Integer

Sheets("Sheet1").Activate

rk = Range("BN2").Value

For Each Cel In Range(Range("BN2"), Range("BN" & Cells.Rows.Count).End(xlUp))
If Cel.Value = rk Then
If WorksheetFunction.CountIf("BN:BN", rk) > 2 Then
copy that entire current row and paste into sheet(duplicate)
Else
check if(cell(cel,"BO") = 1 then
        copy that entire current row and paste into sheet(duplicate) after last row in that sheet
      End If
End If
End If

End If
Next Cel
End Sub

 

[Jeffrey Mahoney]

What is your table ranges on each of the sheets. Are both those rows to be copied going to the same sheet?

I think you have a flaw. Cell BN2 has your value you are checking in Column BN and you are starting with BN2. So if BN2 has the value 12 and there other cells with 12, you will always copy row 2 to the other table.

 

[VBABEGINER]

What is your table ranges on each of the sheets. Are both those rows to be copied going to the same sheet?

I think you have a flaw. Cell BN2 has your value you are checking in Column BN and you are starting with BN2. So if BN2 has the value 12 and there other cells with 12, you will always copy row 2 to the other table.

Hi Jaffrey, I've attached one image of my excel sheet. that is the actual data. I've colored to few row's in green color. Those rows I'm trying to pick in duplicate sheet. As in same format. Condition is BN cell = 1 and BO cell also = 1 then copied those. Since I work very little on vba code, im not that much good writing vba lines, hence asking for help. Let me know if you need something else. Pls suggest here

 

[VBABEGINER]

Hi again, I've attached image again. And tried to write some code as well, very confused dont understand how can I read Col BQ value in Col BN and many more ..

Sub checkRK()
Dim Cel As Range, i As Range
Dim rk As Integer

Dim cn As Integer
cn = Range("BN" & Rows.Count).End(xlUp).Row

Sheets("Sheet1").Activate

Columns("BN:BN").Select
Selection.Copy
Columns("BQ:BQ").Select
ActiveSheet.Paste
Application.CutCopyMode = False
ActiveSheet.Range("$BQ$1:$BQ" & cn).RemoveDuplicates Columns:=1, Header:=xlYes
Range("BQ2").Select

For Each i In Range(Range("BQ2"), Range("BQ" & Cells.Rows.Count).End(xlUp))
Address i
For Each Cel In Range(Range("BN2"), Range("BN" & Cells.Rows.Count).End(xlUp))
If Cel.Value = i Then
rk = WorksheetFunction.CountIf(Range("BN2:BN", cn)) > 2
'copy that entire current row and paste into sheet(duplicate)
    Rows("cel:cel").Select
    Selection.Copy
    Sheets("duplicate").Select
    Dim last_record As Integer
    last_record = Range("A2" & Rows.Count).End(xlUp).Row
    Range("A" & last_record).Select 1
    ActiveSheet.Paste
    Range("A" & last_record).Select
    Next Cel
Else
'check if(cell(cel,"BO") = 1 then
 '       copy that entire current row and paste into sheet(duplicate) after last row in that sheet

End If
End If

End If
Next Cel
Next i
End Sub

 

[VBABEGINER]

What is your table ranges on each of the sheets. Are both those rows to be copied going to the same sheet?

I think you have a flaw. Cell BN2 has your value you are checking in Column BN and you are starting with BN2. So if BN2 has the value 12 and there other cells with 12, you will always copy row 2 to the other table.

Hi Jaffrey, could you please assist. I share 2 post where I tried to show what I'm trying to achieve. Your help will be highly appreciated.

 

[VBABEGINER]

Updated code please.. somehow I got this much.

Sub checkRK()
Dim Cel As Range, i As Range, conCel As Range
Dim rk As Integer

Dim cn As Integer
cn = Range("BN" & Rows.Count).End(xlUp).Row

Sheets("Sheet1").Activate

Columns("BN:BN").Select
Selection.Copy
Sheets("duplicate").Activate
Columns("A:A").Select
ActiveSheet.Paste
Application.CutCopyMode = False
ActiveSheet.Range("$A$1:$A" & cn).RemoveDuplicates Columns:=1, Header:=xlYes
Range("A1").Select

Sheets("Sheet1").Activate

For Each i In Range(Range("BQ2"), Range("BQ" & Cells.Rows.Count).End(xlUp))
'Address i
For Each Cel In Range(Range("BN2"), Range("BN" & Cells.Rows.Count).End(xlUp))
If Cel.Value = i Then
rk = WorksheetFunction.CountIf(Range("BN2", "BN" & cn), i)
If rk < 2 Then
'copy that entire current row and paste into sheet(duplicate)
    Rows("cel:cel").Select
    Selection.Copy
    Sheets("duplicate").Select
    Dim last_record As Integer
    last_record = Range("A2" & Rows.Count).End(xlUp).Row
    Range("A" & last_record).Select
    ActiveSheet.Paste
    Range("A" & last_record).Select
    Exit For
Else
'check if(cell(cel,"BO") = 1 then
 '       copy that entire current row and paste into sheet(duplicate) after last row in that sheet

For Each conCel In Range(Range("BN2"), Range("BN" & Cells.Rows.Count).End(xlUp))
If conCel = rk And Range(conCel + 1, "BO") = 1 Then
    ActiveCell.Row.Select
    Selection.Copy
    Sheets("Selected").Select
    Dim last_record As Integer
    last_record = Range("A2" & Rows.Count).End(xlUp).Row
    Range("A" & last_record).Select
    ActiveSheet.Paste
    Range("A" & last_record).Select
    
End If
Next conCel

End If
End If

'End If
Next Cel
Next i
End Sub

 

[Jeffrey Mahoney]

So, I work a full time job and can sometimes chime and help during the day, but not all the time. You posted 4 times after I went to bed last night and as I was coming to work today.

I want to help. Have more patience. I need to ask questions and I need you to respond to the questions. The code you have provided has me asking more questions. I'll get back to you later.

OK?

 

[VBABEGINER]

So, I work a full time job and can sometimes chime and help during the day, but not all the time. You posted 4 times after I went to bed last night and as I was coming to work today.

I want to help. Have more patience. I need to ask questions and I need you to respond to the questions. The code you have provided has me asking more questions. I'll get back to you later.

OK?

Hi Jeffrey, Yes sure please. Thanks for your reply. I actually posted 4 thing since I was trying bit by bit from my side. hence just updating you the progress. Thanks again for your time. Pls take your time