Macro if condition

  • Thread starter Thread starter puiuluipui
  • Start date Start date
P

puiuluipui

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!
 
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
 
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!
 
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:
 
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
 
Back
Top