| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
IF in vba
Hi, I would like to carry out an IF function so that if one cell has a text
of " Note", text of "N/A" is put in another cell ie =IF(C28="Note","N/A", ""), but I want to do this in VBA so that I can still type in the cell that the answer would be put into, ie if the text isnt "Note" I want to be able to manually write other text or values in the cell. I want this to be automatic without running macros, so that "N/A automatically appears if "Note" is written is the reference cell. VBA is not my strong point but I'm guessing that an IF statement can be used on the correct sheet in Excel objects. |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
IF in vba
You would need to run a macro which is written in VBA.
Right click on the sheet tab and select view code. Put in code like this: Private Sub Worksheet_Calculate() If InStr(1, Range("C28"), "note", vbTextCompare) Then Range("D28") = CVErr(xlErrNA) Else If Range("D28") = CVErr(xlErrNA) Then Range("D28").ClearContents End If End If End Sub Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address = "C28" Then If InStr(1, Range("C28"), "note", vbTextCompare) Then Range("D28") = CVErr(xlErrNA) Else If Range("D28") = CVErr(xlErrNA) Then Range("D28").ClearContents End If End If End If End Sub -- Regards, Tom Ogilvy "Newbeetle" wrote in message ... Hi, I would like to carry out an IF function so that if one cell has a text of " Note", text of "N/A" is put in another cell ie =IF(C28="Note","N/A", ""), but I want to do this in VBA so that I can still type in the cell that the answer would be put into, ie if the text isnt "Note" I want to be able to manually write other text or values in the cell. I want this to be automatic without running macros, so that "N/A automatically appears if "Note" is written is the reference cell. VBA is not my strong point but I'm guessing that an IF statement can be used on the correct sheet in Excel objects. |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
IF in vba
Hi Tom,
I copied the code into a completly new workbook and placed it in Excel objects sheet one, I then went to the Excel sheet and typed note in Cell C28, but the cell D28 stays blank. Any futher ideas appreciated. "Tom Ogilvy" wrote: You would need to run a macro which is written in VBA. Right click on the sheet tab and select view code. Put in code like this: Private Sub Worksheet_Calculate() If InStr(1, Range("C28"), "note", vbTextCompare) Then Range("D28") = CVErr(xlErrNA) Else If Range("D28") = CVErr(xlErrNA) Then Range("D28").ClearContents End If End If End Sub Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address = "C28" Then If InStr(1, Range("C28"), "note", vbTextCompare) Then Range("D28") = CVErr(xlErrNA) Else If Range("D28") = CVErr(xlErrNA) Then Range("D28").ClearContents End If End If End If End Sub -- Regards, Tom Ogilvy "Newbeetle" wrote in message ... Hi, I would like to carry out an IF function so that if one cell has a text of " Note", text of "N/A" is put in another cell ie =IF(C28="Note","N/A", ""), but I want to do this in VBA so that I can still type in the cell that the answer would be put into, ie if the text isnt "Note" I want to be able to manually write other text or values in the cell. I want this to be automatic without running macros, so that "N/A automatically appears if "Note" is written is the reference cell. VBA is not my strong point but I'm guessing that an IF statement can be used on the correct sheet in Excel objects. |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
IF in vba
Change the line in the second event macro from
If Target.Address = "C28" Then to If Target.Address(False, False) = "C28" Then In article , Newbeetle wrote: I copied the code into a completly new workbook and placed it in Excel objects sheet one, I then went to the Excel sheet and typed note in Cell C28, but the cell D28 stays blank. Any futher ideas appreciated. "Tom Ogilvy" wrote: You would need to run a macro which is written in VBA. Right click on the sheet tab and select view code. Put in code like this: Private Sub Worksheet_Calculate() If InStr(1, Range("C28"), "note", vbTextCompare) Then Range("D28") = CVErr(xlErrNA) Else If Range("D28") = CVErr(xlErrNA) Then Range("D28").ClearContents End If End If End Sub Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address = "C28" Then If InStr(1, Range("C28"), "note", vbTextCompare) Then Range("D28") = CVErr(xlErrNA) Else If Range("D28") = CVErr(xlErrNA) Then Range("D28").ClearContents End If End If End If End Sub |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
IF in vba
Thank you
"JE McGimpsey" wrote: Change the line in the second event macro from If Target.Address = "C28" Then to If Target.Address(False, False) = "C28" Then In article , Newbeetle wrote: I copied the code into a completly new workbook and placed it in Excel objects sheet one, I then went to the Excel sheet and typed note in Cell C28, but the cell D28 stays blank. Any futher ideas appreciated. "Tom Ogilvy" wrote: You would need to run a macro which is written in VBA. Right click on the sheet tab and select view code. Put in code like this: Private Sub Worksheet_Calculate() If InStr(1, Range("C28"), "note", vbTextCompare) Then Range("D28") = CVErr(xlErrNA) Else If Range("D28") = CVErr(xlErrNA) Then Range("D28").ClearContents End If End If End Sub Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address = "C28" Then If InStr(1, Range("C28"), "note", vbTextCompare) Then Range("D28") = CVErr(xlErrNA) Else If Range("D28") = CVErr(xlErrNA) Then Range("D28").ClearContents End If End If End If End Sub |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
IF in vba
Thankyou
"JE McGimpsey" wrote: Change the line in the second event macro from If Target.Address = "C28" Then to If Target.Address(False, False) = "C28" Then In article , Newbeetle wrote: I copied the code into a completly new workbook and placed it in Excel objects sheet one, I then went to the Excel sheet and typed note in Cell C28, but the cell D28 stays blank. Any futher ideas appreciated. "Tom Ogilvy" wrote: You would need to run a macro which is written in VBA. Right click on the sheet tab and select view code. Put in code like this: Private Sub Worksheet_Calculate() If InStr(1, Range("C28"), "note", vbTextCompare) Then Range("D28") = CVErr(xlErrNA) Else If Range("D28") = CVErr(xlErrNA) Then Range("D28").ClearContents End If End If End Sub Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address = "C28" Then If InStr(1, Range("C28"), "note", vbTextCompare) Then Range("D28") = CVErr(xlErrNA) Else If Range("D28") = CVErr(xlErrNA) Then Range("D28").ClearContents End If End If End If End Sub |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
IF in vba
Hi,
I have modified the code as below; Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address(False, False) = "C28" Then If InStr(1, Range("C28"), "note", vbTextCompare) Then Range("F28,G28,H28,I28") = "N/A" Else If Range("F28,G28,H28,I28") = "N/A" Then Range("F28,G28,H28,I28").ClearContents End If End If End If End Sub And with all the help thats been given, I'm glad to say it works a treat, But I would now like to do the same for cell C29, to return to F29,G29,H29,I29 then C30 etc but have got a little stuck. "JE McGimpsey" wrote: Change the line in the second event macro from If Target.Address = "C28" Then to If Target.Address(False, False) = "C28" Then In article , Newbeetle wrote: I copied the code into a completly new workbook and placed it in Excel objects sheet one, I then went to the Excel sheet and typed note in Cell C28, but the cell D28 stays blank. Any futher ideas appreciated. "Tom Ogilvy" wrote: You would need to run a macro which is written in VBA. Right click on the sheet tab and select view code. Put in code like this: Private Sub Worksheet_Calculate() If InStr(1, Range("C28"), "note", vbTextCompare) Then Range("D28") = CVErr(xlErrNA) Else If Range("D28") = CVErr(xlErrNA) Then Range("D28").ClearContents End If End If End Sub Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address = "C28" Then If InStr(1, Range("C28"), "note", vbTextCompare) Then Range("D28") = CVErr(xlErrNA) Else If Range("D28") = CVErr(xlErrNA) Then Range("D28").ClearContents End If End If End If End Sub |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
IF in vba
Hi I have modified the code below to;
Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address(False, False) = "C28" Then If InStr(1, Range("C28"), "note", vbTextCompare) Then Range("F28,G28,H28,I28") = "N/A" Else If Range("F28,G28,H28,I28") = "N/A" Then Range("F28,G28,H28,I28").ClearContents End If End If End If End Sub With all the help thats been given I'm glad to say it works a treat, I would now like to do the same for cell C29, to return to F29,G29,H29,I29 but have got a little stuck. "JE McGimpsey" wrote: Change the line in the second event macro from If Target.Address = "C28" Then to If Target.Address(False, False) = "C28" Then In article , Newbeetle wrote: I copied the code into a completly new workbook and placed it in Excel objects sheet one, I then went to the Excel sheet and typed note in Cell C28, but the cell D28 stays blank. Any futher ideas appreciated. "Tom Ogilvy" wrote: You would need to run a macro which is written in VBA. Right click on the sheet tab and select view code. Put in code like this: Private Sub Worksheet_Calculate() If InStr(1, Range("C28"), "note", vbTextCompare) Then Range("D28") = CVErr(xlErrNA) Else If Range("D28") = CVErr(xlErrNA) Then Range("D28").ClearContents End If End If End Sub Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address = "C28" Then If InStr(1, Range("C28"), "note", vbTextCompare) Then Range("D28") = CVErr(xlErrNA) Else If Range("D28") = CVErr(xlErrNA) Then Range("D28").ClearContents End If End If End If End Sub |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
IF in vba
Hi I have modified the code below to;
Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address(False, False) = "C28" Then If InStr(1, Range("C28"), "note", vbTextCompare) Then Range("F28,G28,H28,I28") = "N/A" Else If Range("F28,G28,H28,I28") = "N/A" Then Range("F28,G28,H28,I28").ClearContents End If End If End If End Sub With all the help thats been given I'm glad to say it works a treat, I would now like to do the same for cell C29, to return to F29,G29,H29,I29 but have got a little stuck. "Newbeetle" wrote: Thankyou "JE McGimpsey" wrote: Change the line in the second event macro from If Target.Address = "C28" Then to If Target.Address(False, False) = "C28" Then In article , Newbeetle wrote: I copied the code into a completly new workbook and placed it in Excel objects sheet one, I then went to the Excel sheet and typed note in Cell C28, but the cell D28 stays blank. Any futher ideas appreciated. "Tom Ogilvy" wrote: You would need to run a macro which is written in VBA. Right click on the sheet tab and select view code. Put in code like this: Private Sub Worksheet_Calculate() If InStr(1, Range("C28"), "note", vbTextCompare) Then Range("D28") = CVErr(xlErrNA) Else If Range("D28") = CVErr(xlErrNA) Then Range("D28").ClearContents End If End If End Sub Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address = "C28" Then If InStr(1, Range("C28"), "note", vbTextCompare) Then Range("D28") = CVErr(xlErrNA) Else If Range("D28") = CVErr(xlErrNA) Then Range("D28").ClearContents End If End If End If End Sub |
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
IF in vba
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address(False, False) = "C28" Then If InStr(1, Range("C28"), "note", vbTextCompare) Then Range("F28,G28,H28,I28") = "N/A" Else If Range("F28,G28,H28,I28") = "N/A" Then Range("F28,G28,H28,I28").ClearContents End If End If End If If Target.Address(False, False) = "C29" Then If InStr(1, Range("C29"), "note", vbTextCompare) Then Range("F29,G29,H29,I29") = "N/A" Else If Range("F29,G29,H29,I29") = "N/A" Then Range("F29,G29,H29,I29").ClearContents End If End If End If End Sub this statement: If Range("F28,G28,H28,I28") = "N/A" then is a bit peculiar Just do demonstrate from the immediate window: Range("F28") = "N/A" Range("G28,H28,I28").ClearContents ? Range("F28,G28,H28,I28") = "N/A" True Range("F28").ClearContents Range("G28,H28,I28")= "N/A" ? Range("F28,G28,H28,I28") = "N/A" False so we see that it returns true or false solely on the value of the first cell, F28. that may or may not be what you want. -- Regards, Tom Ogilvy "Newbeetle" wrote: Hi I have modified the code below to; Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address(False, False) = "C28" Then If InStr(1, Range("C28"), "note", vbTextCompare) Then Range("F28,G28,H28,I28") = "N/A" Else If Range("F28,G28,H28,I28") = "N/A" Then Range("F28,G28,H28,I28").ClearContents End If End If End If End Sub With all the help thats been given I'm glad to say it works a treat, I would now like to do the same for cell C29, to return to F29,G29,H29,I29 but have got a little stuck. "JE McGimpsey" wrote: Change the line in the second event macro from If Target.Address = "C28" Then to If Target.Address(False, False) = "C28" Then In article , Newbeetle wrote: I copied the code into a completly new workbook and placed it in Excel objects sheet one, I then went to the Excel sheet and typed note in Cell C28, but the cell D28 stays blank. Any futher ideas appreciated. "Tom Ogilvy" wrote: You would need to run a macro which is written in VBA. Right click on the sheet tab and select view code. Put in code like this: Private Sub Worksheet_Calculate() If InStr(1, Range("C28"), "note", vbTextCompare) Then Range("D28") = CVErr(xlErrNA) Else If Range("D28") = CVErr(xlErrNA) Then Range("D28").ClearContents End If End If End Sub Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address = "C28" Then If InStr(1, Range("C28"), "note", vbTextCompare) Then Range("D28") = CVErr(xlErrNA) Else If Range("D28") = CVErr(xlErrNA) Then Range("D28").ClearContents End If End If End If End Sub |
|
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
IF in vba
Hi Tom,
I could have sworn I tried that, but surely not as that works a treat, thank you as always. "Tom Ogilvy" wrote: Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address(False, False) = "C28" Then If InStr(1, Range("C28"), "note", vbTextCompare) Then Range("F28,G28,H28,I28") = "N/A" Else If Range("F28,G28,H28,I28") = "N/A" Then Range("F28,G28,H28,I28").ClearContents End If End If End If If Target.Address(False, False) = "C29" Then If InStr(1, Range("C29"), "note", vbTextCompare) Then Range("F29,G29,H29,I29") = "N/A" Else If Range("F29,G29,H29,I29") = "N/A" Then Range("F29,G29,H29,I29").ClearContents End If End If End If End Sub this statement: If Range("F28,G28,H28,I28") = "N/A" then is a bit peculiar Just do demonstrate from the immediate window: Range("F28") = "N/A" Range("G28,H28,I28").ClearContents ? Range("F28,G28,H28,I28") = "N/A" True Range("F28").ClearContents Range("G28,H28,I28")= "N/A" ? Range("F28,G28,H28,I28") = "N/A" False so we see that it returns true or false solely on the value of the first cell, F28. that may or may not be what you want. -- Regards, Tom Ogilvy "Newbeetle" wrote: Hi I have modified the code below to; Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address(False, False) = "C28" Then If InStr(1, Range("C28"), "note", vbTextCompare) Then Range("F28,G28,H28,I28") = "N/A" Else If Range("F28,G28,H28,I28") = "N/A" Then Range("F28,G28,H28,I28").ClearContents End If End If End If End Sub With all the help thats been given I'm glad to say it works a treat, I would now like to do the same for cell C29, to return to F29,G29,H29,I29 but have got a little stuck. "JE McGimpsey" wrote: Change the line in the second event macro from If Target.Address = "C28" Then to If Target.Address(False, False) = "C28" Then In article , Newbeetle wrote: I copied the code into a completly new workbook and placed it in Excel objects sheet one, I then went to the Excel sheet and typed note in Cell C28, but the cell D28 stays blank. Any futher ideas appreciated. "Tom Ogilvy" wrote: You would need to run a macro which is written in VBA. Right click on the sheet tab and select view code. Put in code like this: Private Sub Worksheet_Calculate() If InStr(1, Range("C28"), "note", vbTextCompare) Then Range("D28") = CVErr(xlErrNA) Else If Range("D28") = CVErr(xlErrNA) Then Range("D28").ClearContents End If End If End Sub Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address = "C28" Then If InStr(1, Range("C28"), "note", vbTextCompare) Then Range("D28") = CVErr(xlErrNA) Else If Range("D28") = CVErr(xlErrNA) Then Range("D28").ClearContents End If End If End If End Sub |
|
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
IF in vba
Thanks Tom, I could of sworn i had tried that, but surley not as that works a treat, thank you for you help as always. "Tom Ogilvy" wrote: Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address(False, False) = "C28" Then If InStr(1, Range("C28"), "note", vbTextCompare) Then Range("F28,G28,H28,I28") = "N/A" Else If Range("F28,G28,H28,I28") = "N/A" Then Range("F28,G28,H28,I28").ClearContents End If End If End If If Target.Address(False, False) = "C29" Then If InStr(1, Range("C29"), "note", vbTextCompare) Then Range("F29,G29,H29,I29") = "N/A" Else If Range("F29,G29,H29,I29") = "N/A" Then Range("F29,G29,H29,I29").ClearContents End If End If End If End Sub this statement: If Range("F28,G28,H28,I28") = "N/A" then is a bit peculiar Just do demonstrate from the immediate window: Range("F28") = "N/A" Range("G28,H28,I28").ClearContents ? Range("F28,G28,H28,I28") = "N/A" True Range("F28").ClearContents Range("G28,H28,I28")= "N/A" ? Range("F28,G28,H28,I28") = "N/A" False so we see that it returns true or false solely on the value of the first cell, F28. that may or may not be what you want. -- Regards, Tom Ogilvy "Newbeetle" wrote: Hi I have modified the code below to; Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address(False, False) = "C28" Then If InStr(1, Range("C28"), "note", vbTextCompare) Then Range("F28,G28,H28,I28") = "N/A" Else If Range("F28,G28,H28,I28") = "N/A" Then Range("F28,G28,H28,I28").ClearContents End If End If End If End Sub With all the help thats been given I'm glad to say it works a treat, I would now like to do the same for cell C29, to return to F29,G29,H29,I29 but have got a little stuck. "JE McGimpsey" wrote: Change the line in the second event macro from If Target.Address = "C28" Then to If Target.Address(False, False) = "C28" Then In article , Newbeetle wrote: I copied the code into a completly new workbook and placed it in Excel objects sheet one, I then went to the Excel sheet and typed note in Cell C28, but the cell D28 stays blank. Any futher ideas appreciated. "Tom Ogilvy" wrote: You would need to run a macro which is written in VBA. Right click on the sheet tab and select view code. Put in code like this: Private Sub Worksheet_Calculate() If InStr(1, Range("C28"), "note", vbTextCompare) Then Range("D28") = CVErr(xlErrNA) Else If Range("D28") = CVErr(xlErrNA) Then Range("D28").ClearContents End If End If End Sub Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address = "C28" Then If InStr(1, Range("C28"), "note", vbTextCompare) Then Range("D28") = CVErr(xlErrNA) Else If Range("D28") = CVErr(xlErrNA) Then Range("D28").ClearContents End If End If End If End Sub |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Linear Mode
