Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
Solved

# Loop down and make minus

Posted on 2011-09-29
Medium Priority
146 Views
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
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
• 2
• 2

LVL 24

Expert Comment

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
Do
rFind.Offset(, 5) = -1 * rFind.Offset(, 5)
Set rFind = .FindNext(rFind)
End If
End With

End Sub
``````
0

Author Comment

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

LVL 24

Accepted Solution

StephenJR earned 2000 total points
ID: 36814854
Change line 5 to

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

Author Closing Comment

ID: 36814916
Cheers Stephen!
Seamus
0

## Featured Post

Question has a verified solution.

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

Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
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…
###### Suggested Courses
Course of the Month11 days, 11 hours left to enroll