Power Query Course in Spanish
Thanks Thanks:  0
Likes Likes:  0
Page 2 of 2 FirstFirst 12
Results 11 to 14 of 14

Thread: BIG IF

  1. #11
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    83,637
    Post Thanks / Like
    Mentioned
    33 Post(s)
    Tagged
    6 Thread(s)

    Default

    On 2002-04-18 02:53, swaink wrote:
    Hi Guys

    Just to clarify where Im at.
    On sheet 1 I'm using code and a button, the user will click this button to confirm a bookin. If the button is clicked it will update say cell C9 to read TRUE.

    Now in another cell (G12) using Aladins formula "Data!"&ADDRESS(MATCH(C12,DATES,0)+2,2)I have obtained the address of a cell on Sheet 2.

    If the value in C9 is false I don't want to do anything, but if its TRUE then I want the cell on Sheet2 to read "Booked"

    So Im trying to use the address retreived by the code in G12 as a reference point.

    Phew I hope that makes sense
    I think you need code (VBA) that reads the cell address returned and moves to the cell, check if it's flagged TRUE, and changes the cell content to "Booked", if flagged FALSE, does nothing. You'll loose any formula that you might have there. Something for Mark O, I guess.

    Aladin



    [ This Message was edited by: Aladin Akyurek on 2002-04-18 05:00 ]

  2. #12
    Board Regular swaink's Avatar
    Join Date
    Feb 2002
    Location
    51.421818,-0.977139
    Posts
    432
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi Aladin

    It certainly gets the grey cells working mate.

    Kev

  3. #13
    MrExcel MVP Mark O'Brien's Avatar
    Join Date
    Feb 2002
    Location
    Columbus, OH, USA
    Posts
    3,530
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I knew there was VBA somewhere in this thread.

    OK, I think I understand what you want, but I could be wrong.

    What you are doing is pressing a button that changes the value of C9 on Sheet1 to TRUE. Now if this value is TRUE, you want to look in cell G12 on sheet2 to see what cell reference is there. (If I'm reading Aladin's formula correctly, that would return something like "Data!$A$1")

    So using this example, if C9 is TRUE, I would put "Booked" into cell A1 on the worksheet named "Data". To coin an expression, phew!

    OK, here's what I've got:


    Public Sub main()

    Dim oRange As Range

    Set oRange = Sheets("Sheet2").Range("G12")

    'You don't need this If...Then statement if you call this sub only when you set C9 to TRUE
    If Sheets("Sheet1").Range("C9").Value = True Then
    Sheets(LeftString(oRange.Value, "!")).Range(RightString(oRange.Value, "!")).Value = "Booked"
    Else
    MsgBox "Is False"
    End If

    End Sub
    Public Function LeftString(ByVal sText As String, ByVal sSeparator As String) As String
    LeftString = Left(sText, InStr(1, sText, sSeparator) - 1)
    End Function
    Public Function RightString(ByVal sText As String, ByVal sSeparator As String) As String
    RightString = Right(sText, Len(sText) - InStr(1, sText, sSeparator))
    End Function


    I've slipped in a couple of functions that I used to use a lot, for free. They're required to get the address from the value in cell G12.

    To use this:

    You will copy and paste this into the same module that your button click code is on.
    In your button_click code, you would invoke the subroutine "main" after you have set the value of C9 to TRUE. (I would suggest changing the name of the subroutine "main" to something that makes more sense to you, like "UpdateCell" or something)

    Oh yeah, to call "main" simply put the word "main" in your code or you can use "Call main" to make it more explicit.

    HTH

    (If this isn't what you're looking for repost with what you really need)

  4. #14
    Board Regular swaink's Avatar
    Join Date
    Feb 2002
    Location
    51.421818,-0.977139
    Posts
    432
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi Mark

    I worked on that last night and it works fine

    Many thanks

    Kev

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •