|
| If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|||||||
|
|
Thread Tools | Display Modes |
|
#1
November 13th, 2009, 09:44 AM
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
November 13th, 2009, 10:13 AM
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
November 13th, 2009, 10:24 AM
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!
|
|
#4
November 13th, 2009, 11:31 AM
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!
|
|
#5
November 13th, 2009, 11:45 AM
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!
|
|
#6
November 13th, 2009, 12:07 PM
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!
|
| Thread Tools | |
| Display Modes | |
|
|
Linear Mode
