?
Solved

Explanation of VB Code in Excel spreadsheet

Posted on 2014-09-03
4
Medium Priority
?
157 Views
Last Modified: 2014-09-04
Experts,

I had this code in an Excel spreadsheet to delete rows that had values < 0.  I want to use this logic again, but I am not sure  what this statement means.

It is  Cells(i + 3, j * 3 + 1)  that I need an explanation of

            If Cells(i + 3, j * 3 + 1) <> 0 And Len(Cells(i + 3, j * 3 + 1)) > 0 Then GoTo notDelete
0
Comment
Question by:morinia
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 33

Assisted Solution

by:Rob Henson
Rob Henson earned 1000 total points
ID: 40301145
This uses the Cells function to identify the cell reference, it uses the following syntax:

Cells(Row,Column) where Row and Column are integers representing row and column numbers.

Your code has the Row and Column numbers set by variables i and j, these will have been set to a value earlier in the code.

Thanks
Rob H
0
 
LVL 49

Accepted Solution

by:
Martin Liss earned 1000 total points
ID: 40301154
The Cells() function is Cells(Row number, Column number)

i + 3 is a variable named "i" incremented by 3 and it is the row number
j * 3 + 1 is another variable named "j" multiplied by 3 before adding 1 and used as the column number
0
 
LVL 49

Expert Comment

by:Martin Liss
ID: 40301254
I'm glad I was able to help.

In my profile you'll find links to some articles I've written that may interest you.
Marty - MVP 2009 to 2014
0
 
LVL 35

Expert Comment

by:[ fanpages ]
ID: 40303100
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

764 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question