Wrox Programmer Forums
|
Excel VBA Discuss using VBA for Excel programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Excel VBA section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old January 18th, 2005, 07:25 AM
Authorized User
 
Join Date: Dec 2004
Posts: 43
Thanks: 0
Thanked 0 Times in 0 Posts
Default Loops

Hi

I have 2 columns. 1 column contains figures and the next column contains either the letters "CR" or "DR" (for creit or debit). I am trying to loop around the cr/dr column find any cell with DR and multiply the column next to it by -1 (or put minus sign in). Looping around the cr-dr col is fine but when you locate DR, how do you tell access to loop around the next col until its at the cell directly next to DR?

Cheers

Cheers
Tony
__________________
Cheers
Tony
 
Old January 18th, 2005, 10:34 AM
Authorized User
 
Join Date: Jun 2003
Posts: 13
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Hi Tony--

  You want to use the offset function:

For Each cell In myRange
  If cell.Value = "d" Then
    cell.Offset(0, -1).Value = cell.Offset(0, -1).Value * -1
  End If
Next

The offset of (0, -1) means that the cell with the value is in the same row (row offset = 0) 1 column to the left (col offset = -1) of the cell with the identifier

  Offsets are a powerful tool useful in many situations

Enjoy!

Barry

 
Old January 20th, 2005, 07:40 AM
Authorized User
 
Join Date: Dec 2004
Posts: 43
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Works a trest....thanks a mill.

Cheers


Cheers
Tony





Similar Threads
Thread Thread Starter Forum Replies Last Post
break for-each loops, or limit amount of loops warhero XSLT 2 July 4th, 2007 02:18 AM
Loops deontae45 VB.NET 2002/2003 Basics 2 September 28th, 2006 03:48 PM
Two Loops iloveoatmeal Classic ASP Basics 5 September 26th, 2005 09:59 AM
While loops and For loops in XSLT spencer.clark XSLT 1 August 5th, 2005 09:50 AM
loops Hudson40 Access VBA 1 February 4th, 2005 12:58 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.