1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Excel 2003 replace macro based on value in other cell

Discussion in 'Business Applications' started by whschimmel, Dec 21, 2011.

Thread Status:
Not open for further replies.
Advertisement
  1. whschimmel

    whschimmel Thread Starter

    Joined:
    Jul 7, 2008
    Messages:
    168
    Hello

    I need to do the following in VBA:

    If cell in column C contains the value GBP, change cell S from that row from 13 to 23
    or
    If a cell column C contains the value USD change cell S from that row from 13 to 33

    exception cell: C1 --> contains the title of the column

    How do I do this?
     
  2. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,553
    First Name:
    Hans
    In the loop of your VBA (going down the C column)

    For each xrow form 2 to ....
    If cells(xrow,"C") = "GBP"Then
    cells(xrow, "S") = 23
    Elseif Cells(xrow,"C") = 'USD" Them
    cells(xrow, "S") = 33
    End if

    Just translate your text to code

    If cell x contains that then S = that else if cell x contains this then s = this etc etc

    I hope this helps :)
     
  3. whschimmel

    whschimmel Thread Starter

    Joined:
    Jul 7, 2008
    Messages:
    168
    think its time for a VBA course for me
     
  4. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,553
    First Name:
    Hans
    :) never too late to learn :)
     
  5. bomb #21

    bomb #21

    Joined:
    Jul 1, 2005
    Messages:
    8,546
    OP, what is "contains"? C2 could be just (example) "GBP" or "Price in GBP".

    The InStr function finds the starting position of a string within another, if it's there. So if the cell is "GBP" then InStr("GBPUSD", Cell) returns 1. If the cell is "USD" InStr("GBPUSD", Cell) returns 4.

    Thus:

    Sub test()
    For Each Cell In Range("C2:C" & Range("C" & Rows.Count).End(xlUp).Row)
    Select Case InStr("GBPUSD", Cell)
    Case 1
    Range("S" & Cell.Row) = 23
    Case 4
    Range("S" & Cell.Row) = 33
    End Select
    Next Cell
    End Sub
     
  6. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/1032245

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice