CODEWITHSUNDEEP
vbaexcel
user2881181
user2881181

Reputation:

Do Loop and If Statement in Excel

In the WorkSheet, I'm trying to create the following logic in Excel Macro.

Logic:

Dub Test()

Dim rws as integer
Dim counter as integer

rws = 6   
counter = 2

Do while rws <> 0
    IF 'Data Sheet with Space'!J`counter` = "Sample Data Row 1 Col 10" Then
        WorkSheet!A`counter` = 'Data Sheet with Space'!A`counter`
    end IF

    counter = counter + 1
    rws = rws - 1

The Excel sheets are:

Data Sheet with Space

---------------------------------------------
Header 1                 Header 2                 Header 3                 Header 4                 Header 5                 Header 6                 Header 7                 Header 8                 Header 9                 Header 10                
Sample Data Row 1 Col 1  Sample Data Row 1 Col 2  Sample Data Row 1 Col 3  Sample Data Row 1 Col 4  Sample Data Row 1 Col 5  Sample Data Row 1 Col 6  Sample Data Row 1 Col 7  Sample Data Row 1 Col 8  Sample Data Row 1 Col 9  Sample Data Row 1 Col 10 
Sample Data Row 2 Col 1  Sample Data Row 2 Col 2  Sample Data Row 2 Col 3  Sample Data Row 2 Col 4  Sample Data Row 2 Col 5  Sample Data Row 2 Col 6  Sample Data Row 2 Col 7  Sample Data Row 2 Col 8  Sample Data Row 2 Col 9  Sample Data Row 2 Col 10 
Sample Data Row 3 Col 1  Sample Data Row 3 Col 2  Sample Data Row 3 Col 3  Sample Data Row 3 Col 4  Sample Data Row 3 Col 5  Sample Data Row 3 Col 6  Sample Data Row 3 Col 7  Sample Data Row 3 Col 8  Sample Data Row 3 Col 9  Sample Data Row 3 Col 10 
Sample Data Row 4 Col 1  Sample Data Row 4 Col 2  Sample Data Row 4 Col 3  Sample Data Row 4 Col 4  Sample Data Row 4 Col 5  Sample Data Row 4 Col 6  Sample Data Row 4 Col 7  Sample Data Row 4 Col 8  Sample Data Row 4 Col 9  Sample Data Row 4 Col 10 
Sample Data Row 5 Col 1  Sample Data Row 5 Col 2  Sample Data Row 5 Col 3  Sample Data Row 5 Col 4  Sample Data Row 5 Col 5  Sample Data Row 5 Col 6  Sample Data Row 5 Col 7  Sample Data Row 5 Col 8  Sample Data Row 5 Col 9  Sample Data Row 5 Col 10 
---------------------------------------------

and the other Excel sheet called WorkSheet

When trying to run the macro I get an error the ' in the Data Sheet with Space name causes an error.

Secondly, I an having an error when trying to change the input and output cell number dynamically using the counter variable.

Any help is appreciate it.

Upvotes: 0

Views: 71

Answers (2)

Pierre44
Pierre44

Reputation: 1741

The error comes from your ' in the code. (Easily visible already here in grey). If I understood your code correctly then you should correct it by entering real sheets and range as below (not tested):

Sub Test2()

Dim rws As Integer
Dim counter As Integer

rws = 6
counter = 2

Do While rws <> 0

If Sheets("Data Sheet with Space").Range("J" & counter).Value = Cells(1, 10).Value Then


Sheets("WorkSheet").Range("A" & counter).Value = Sheets("Data Sheet with Space").Range("A" & counter).Value

End If

counter = counter + 1
rws = rws - 1
Loop 

End Sub

Upvotes: 0

Peter Pesch
Peter Pesch

Reputation: 643

The problem lies in the way in which you address the cells.

If you want to refer to cell "B6" on the first sheet, there are basically 3 ways to refer to the sheet:

  1. By CodeName (If you happen to know the codename of the sheet)

    Sheet1.[B6]

  2. By Name (That is the name you see on the tab of the sheet)

    Sheets("Data Sheet with Space").[B6]

  3. By Number (If you happen to know the order of the sheets)

    Sheets(1).[B6]

It is your code, you are going to maintain it, so use whichever looks best to you. You might prefer to use a variable: wsData = Sheets("Data Sheet with Space"), so you could simply write wsOther.[A1]=wsData.[B6].

There are also several ways to refer to the cells on the sheet. Some usefull ones:

  1. Directly using the Address:

    Sheets("Data Sheet with Space").[B6]

  2. By using .Range (using the Address)

    Sheets("Data Sheet with Space").Range("B6")

  3. By using .Cell (using the Coordinates)

    Sheets("Data Sheet with Space").Cells(6, 2)

  4. A more reeadable way of using .Cell (as sugessted by YowE3K)

    Sheets("Data Sheet with Space").Cells(6, "B")

In your code, you are already using coordinates. So you might prefer to use .Cells(rws, counter). But it's up to you.

Upvotes: 2

Related Questions