Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 149
  • Last Modified:

Loop down and make minus

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
Seamus2626
Asked:
Seamus2626
  • 2
  • 2
1 Solution
 
StephenJRCommented:
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
 
Seamus2626Author Commented:
Looks good Stephen, how can i set sheet1 = to the tab name (Raw Data)
0
 
StephenJRCommented:
Change line 5 to

With Sheets("Raw Data").Columns(10)
0
 
Seamus2626Author Commented:
Cheers Stephen!
Seamus
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now