| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro if condition
Hi, i need a macro to add some text if a value is found in "C" column.
If in C column i write "Inv", then to add from next cell this : (name,val,date,reason) and to format all to arial bold 12 If in C column i write "Pen", then to add from next cell this : (name,time,date,reason) and to format all to arial bold 12 Ex: Inv name val date reason Pen name tine date reason Can this be done? Thanks! |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Macro if condition
Give this a try..
Sub Macro() Dim varFound As Variant, arrFind As Variant, strAddress As String Dim intFind As Integer arrFind = Array("Inv", "Pen") For intFind = 0 To UBound(arrFind) With ActiveSheet.Range("C:C") Set varFound = .Find(arrFind(intFind), , xlValues, 1) If Not varFound Is Nothing Then strAddress = varFound.Address Do Range("D" & varFound.Row).Resize(, 4) = Split("name,val,date,reason", ",") With Range("D" & varFound.Row).Resize(, 4).Font .Name = "Arial": .Size = 12: .Bold = True End With Set varFound = .FindNext(varFound) Loop While Not varFound Is Nothing And _ varFound.Address < strAddress End If End With Next End Sub If this post helps click Yes --------------- Jacob Skaria "puiuluipui" wrote: Hi, i need a macro to add some text if a value is found in "C" column. If in C column i write "Inv", then to add from next cell this : (name,val,date,reason) and to format all to arial bold 12 If in C column i write "Pen", then to add from next cell this : (name,time,date,reason) and to format all to arial bold 12 Ex: Inv name val date reason Pen name tine date reason Can this be done? Thanks! |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Macro if condition
Hi Jacob, it's working, but i need the code to work by itself. I write "Inv"
somwere in "C" column and the code insert the words. Now it's working if i manually run the code. I need the code to work when i hit enter or i select next cell. Can this be done? Thanks! "Jacob Skaria" a scris: Give this a try.. Sub Macro() Dim varFound As Variant, arrFind As Variant, strAddress As String Dim intFind As Integer arrFind = Array("Inv", "Pen") For intFind = 0 To UBound(arrFind) With ActiveSheet.Range("C:C") Set varFound = .Find(arrFind(intFind), , xlValues, 1) If Not varFound Is Nothing Then strAddress = varFound.Address Do Range("D" & varFound.Row).Resize(, 4) = Split("name,val,date,reason", ",") With Range("D" & varFound.Row).Resize(, 4).Font .Name = "Arial": .Size = 12: .Bold = True End With Set varFound = .FindNext(varFound) Loop While Not varFound Is Nothing And _ varFound.Address < strAddress End If End With Next End Sub If this post helps click Yes --------------- Jacob Skaria "puiuluipui" wrote: Hi, i need a macro to add some text if a value is found in "C" column. If in C column i write "Inv", then to add from next cell this : (name,val,date,reason) and to format all to arial bold 12 If in C column i write "Pen", then to add from next cell this : (name,time,date,reason) and to format all to arial bold 12 Ex: Inv name val date reason Pen name tine date reason Can this be done? Thanks! |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Macro if condition
Select the sheet tab which you want to work with. Right click the sheet tab
and click on 'View Code'. This will launch VBE. Paste the below code to the right blank portion. Get back to to workbook and try out. Private Sub Worksheet_Change(ByVal Target As Range) Application.EnableEvents = False If Target.Count = 1 And Target.Column = 3 Then If UCase(Target.Text) = "INV" Or UCase(Target.Text) = "PEN" Then Range("D" & Target.Row).Resize(, 4) = Split("name,val,date,reason", ",") With Range("C" & Target.Row).Resize(, 5).Font .Name = "Arial": .Size = 12: .Bold = True End With End If End If Application.EnableEvents = True End Sub If this post helps click Yes --------------- Jacob Skaria "puiuluipui" wrote: Hi Jacob, it's working, but i need the code to work by itself. I write "Inv" somwere in "C" column and the code insert the words. Now it's working if i manually run the code. I need the code to work when i hit enter or i select next cell. Can this be done? Thanks! "Jacob Skaria" a scris: Give this a try.. Sub Macro() Dim varFound As Variant, arrFind As Variant, strAddress As String Dim intFind As Integer arrFind = Array("Inv", "Pen") For intFind = 0 To UBound(arrFind) With ActiveSheet.Range("C:C") Set varFound = .Find(arrFind(intFind), , xlValues, 1) If Not varFound Is Nothing Then strAddress = varFound.Address Do Range("D" & varFound.Row).Resize(, 4) = Split("name,val,date,reason", ",") With Range("D" & varFound.Row).Resize(, 4).Font .Name = "Arial": .Size = 12: .Bold = True End With Set varFound = .FindNext(varFound) Loop While Not varFound Is Nothing And _ varFound.Address < strAddress End If End With Next End Sub If this post helps click Yes --------------- Jacob Skaria "puiuluipui" wrote: Hi, i need a macro to add some text if a value is found in "C" column. If in C column i write "Inv", then to add from next cell this : (name,val,date,reason) and to format all to arial bold 12 If in C column i write "Pen", then to add from next cell this : (name,time,date,reason) and to format all to arial bold 12 Ex: Inv name val date reason Pen name tine date reason Can this be done? Thanks! |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Macro if condition
It's working!
Thanks allot Jacob! "Jacob Skaria" a scris: Select the sheet tab which you want to work with. Right click the sheet tab and click on 'View Code'. This will launch VBE. Paste the below code to the right blank portion. Get back to to workbook and try out. Private Sub Worksheet_Change(ByVal Target As Range) Application.EnableEvents = False If Target.Count = 1 And Target.Column = 3 Then If UCase(Target.Text) = "INV" Or UCase(Target.Text) = "PEN" Then Range("D" & Target.Row).Resize(, 4) = Split("name,val,date,reason", ",") With Range("C" & Target.Row).Resize(, 5).Font .Name = "Arial": .Size = 12: .Bold = True End With End If End If Application.EnableEvents = True End Sub If this post helps click Yes --------------- Jacob Skaria "puiuluipui" wrote: Hi Jacob, it's working, but i need the code to work by itself. I write "Inv" somwere in "C" column and the code insert the words. Now it's working if i manually run the code. I need the code to work when i hit enter or i select next cell. Can this be done? Thanks! "Jacob Skaria" a scris: Give this a try.. Sub Macro() Dim varFound As Variant, arrFind As Variant, strAddress As String Dim intFind As Integer arrFind = Array("Inv", "Pen") For intFind = 0 To UBound(arrFind) With ActiveSheet.Range("C:C") Set varFound = .Find(arrFind(intFind), , xlValues, 1) If Not varFound Is Nothing Then strAddress = varFound.Address Do Range("D" & varFound.Row).Resize(, 4) = Split("name,val,date,reason", ",") With Range("D" & varFound.Row).Resize(, 4).Font .Name = "Arial": .Size = 12: .Bold = True End With Set varFound = .FindNext(varFound) Loop While Not varFound Is Nothing And _ varFound.Address < strAddress End If End With Next End Sub If this post helps click Yes --------------- Jacob Skaria "puiuluipui" wrote: Hi, i need a macro to add some text if a value is found in "C" column. If in C column i write "Inv", then to add from next cell this : (name,val,date,reason) and to format all to arial bold 12 If in C column i write "Pen", then to add from next cell this : (name,time,date,reason) and to format all to arial bold 12 Ex: Inv name val date reason Pen name tine date reason Can this be done? Thanks! |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Macro if condition
The Column you are applying the Macro will always have the same format that
is Arial Bold 12 for all the values then you can do it using the IF function itself. I assume that your range starts from C column and end with G Column (i.e.) the value €śInv€ť is in C1 and the value €śreason€ť is in G1 then paste the below formula in H1. =IF(C1="","No Value in C Column Cell",IF(OR(C1="Inv",C1="Pen"),D1&", "&E1&", "&F1&", "&G1,"")) Just Format the Column as Arial Bold 12. If you want to differentiate the Format of the resulting values based on the C Column criteria then I think it will not be useful for u€¦ If this post helps, Click Yes! -------------------- (Ms-Exl-Learner) -------------------- "puiuluipui" wrote: Hi, i need a macro to add some text if a value is found in "C" column. If in C column i write "Inv", then to add from next cell this : (name,val,date,reason) and to format all to arial bold 12 If in C column i write "Pen", then to add from next cell this : (name,time,date,reason) and to format all to arial bold 12 Ex: Inv name val date reason Pen name tine date reason Can this be done? Thanks! |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Macro to populate text when condition is met | Excel Worksheet Functions | |||
| Insert rows with condition using macro | Excel Discussion (Misc queries) | |||
| use more than 4 condition in cf without using macro | Excel Worksheet Functions | |||
| event macro on a condition | Excel Discussion (Misc queries) | |||
| How Do I Hide A Row (if a condition is true) using a Macro ? | Excel Worksheet Functions | |||
Hybrid Mode
