Solved

Loop down and make minus

Posted on 2011-09-29
4
145 Views
Last Modified: 2012-05-12
Hi,

I am looking for some vba code to loop down Column J, and if it finds the word "Sel" then multiply the adjacent cell in Column O by -1

Has anyone anything for that?

Thanks
Seamus
0
Comment
Question by:Seamus2626
[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
  • 2
4 Comments
 
LVL 24

Expert Comment

by:StephenJR
ID: 36814805
Seamus - try this:
Sub x()
  
Dim rFind As Range, sAddr As String
 
With Sheet1.Columns(10)
    Set rFind = .Find(What:="Sel", LookAt:=xlWhole, MatchCase:=False, SearchFormat:=False)
    If Not rFind Is Nothing Then
        sAddr = rFind.Address
        Do
            rFind.Offset(, 5) = -1 * rFind.Offset(, 5)
            Set rFind = .FindNext(rFind)
         Loop While rFind.Address <> sAddr
    End If
End With
     
End Sub

Open in new window

0
 

Author Comment

by:Seamus2626
ID: 36814829
Looks good Stephen, how can i set sheet1 = to the tab name (Raw Data)
0
 
LVL 24

Accepted Solution

by:
StephenJR earned 500 total points
ID: 36814854
Change line 5 to

With Sheets("Raw Data").Columns(10)
0
 

Author Closing Comment

by:Seamus2626
ID: 36814916
Cheers Stephen!
Seamus
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say 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

Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
This article describes a serious pitfall that can happen when deleting shapes using VBA.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

617 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