1

hope anyone can help me with my VBA code.

It now says: If cell.Value = "ja" And (cell.Offset(0, 9) = "meenemen") Then...

I would like it to be something like: If cell.Value = "ja" And (cell.Offset(0, 9) = "meenemen") or ((cell.Offset(1, 0) = "ja" and (cell.Offset(1, 9) = "meenemen")) Then...

I now use: For Each row In rng.Rows But I would like it to ONLY check every other row (and in another part of my coding every 6 th row) before proceding.

Unfortunately I cannot change the layout of the worksheet where the data is loaded in.

Does anyone has a nice solution for me? Thanks in advance for your effort to help me.

The part of the VBA code it concerns looks like this now:

Sub CheckRange3()

Dim rng As Range Dim row As Range Dim cell As Range

Set rng = Range("C47:C66")

For Each row In rng.Rows For Each cell In row.Cells If cell.Value = "ja" And (cell.Offset(0, 9) = "meenemen") Then

    Dim Name As String
    DateStr = Format(Date, "dd-mm-yy")
    Name = cell.Offset(0, -1).Text & "-" & Range("A44") & " " & DateStr
    cell.Offset(0, 12).Value = Name

    Dim startPath As String
    Dim myName As String

    startPath = "I:\Medische Microbiologie\Virologie\Sequence-resultaten\@In bewerking\"
    myName = cell.Offset(0, 12).Text       ' Change as required to cell holding the folder title

    If myName = vbNullString Then myName = "Testing"

    Dim folderPathWithName As String
    folderPathWithName = startPath & Application.PathSeparator & myName

    If Dir(folderPathWithName, vbDirectory) = vbNullString Then
        MkDir folderPathWithName
    Else
        On Error Resume Next
       'Delete files
        FSO.deletefile MyPath & "\*.*", False
        'Delete subfolders
        FSO.deletefolder MyPath & "\*.*", False
        On Error GoTo 0

    End If

    ThisWorkbook.Sheets("BLAST resultaten LSU-ITS").Range("F6").Value = cell.Offset(0, 6).Text
    ThisWorkbook.Sheets("BLAST resultaten LSU-ITS").Range("A10").Value = cell.Offset(0, -1).Text
    ThisWorkbook.Sheets("BLAST resultaten LSU-ITS").Range("B10").Value = cell.Offset(0, 7).Text
    ThisWorkbook.Sheets("BLAST resultaten LSU-ITS").Range("C10").Value = cell.Offset(0, 1).Text
    ThisWorkbook.Sheets("BLAST resultaten LSU-ITS").Range("E10").Value = cell.Offset(0, 4).Text
    ThisWorkbook.Sheets("BLAST resultaten LSU-ITS").Range("F10").Value = cell.Offset(0, 5).Text
    ThisWorkbook.Sheets("BLAST resultaten LSU-ITS").Range("G10").Value = cell.Offset(0, 8).Text
    ThisWorkbook.Sheets("BLAST resultaten LSU-ITS").Range("E11").Value = cell.Offset(1, 4).Text
    ThisWorkbook.Sheets("BLAST resultaten LSU-ITS").Range("F11").Value = cell.Offset(1, 5).Text
    ThisWorkbook.Sheets("BLAST resultaten LSU-ITS").Range("G11").Value = cell.Offset(1, 8).Text

    ThisWorkbook.Sheets("BLAST resultaten LSU-ITS").Copy
    ActiveWorkbook.SaveAs (startPath & myName & "\BLAST resultaten " & Name)
    ActiveWorkbook.Close

Else
    cell.Offset(0, 12).ClearContents
End If

Next cell Next row

'FolderInBewerking

End Sub

1

You should use a For Next loop and the Step keyword to specify an increment.

Dim rowIterator As Long
For rowIterator = 1 To rng.Rows.Count Step 2 ' Will go through every other row from the first to the last (or last-1 depending on parity of your number of rows)
    Set row = rng.Rows(rowIterator)
    ' Your code
Next rowIterator
  • Thank you for the quick reply. I added your code and changed If cell.Value = "ja" And (cell.Offset(0, 9) = "meenemen") Then... to If cell.Value = "ja" And (cell.Offset(0, 9) = "meenemen") Or (cell.Offset(1, 9) = "meenemen") Then... and now it works like a charm. Perfect, thank you verg much! – Caroline Mar 1 at 10:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.