BIG IF

[swaink]

Hi Everyone

I have a formula in one cell that returns TRUE/FALSE.

If the value is TRUE I want to update a cell on another worksheet to read "Booked"

Can anyone Help

Kev

 

[Aladin Akyurek]

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

 

[swaink]

Hi Aladin

It certainly gets the grey cells working mate.

Kev

 

[Mark O'Brien]

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:

<pre>
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</pre>

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)

 

[swaink]

Hi Mark

I worked on that last night and it works fine

Many thanks

Kev