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
Solved

Explanation of VB Code in Excel spreadsheet

Posted on 2014-09-03
4
151 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
  • 2
4 Comments
 
LVL 33

Assisted Solution

by:Rob Henson
Rob Henson earned 250 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 46

Accepted Solution

by:
Martin Liss earned 250 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 46

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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Deploying a Microsoft Access application in a Citrix environment is not difficult but takes a few steps. However, Citrix system people are often of little help, as they typically know next to nothing about Access. The script provided here will take …
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

807 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