Advice on macro formula

[Andyb2]

Hi all,

I want to run a macro that does the following every time I open a salary sheet:

1. If column N displays the code 2102, 2111, 2112, 2201, 2101, 2301, 2312 then copy the name displayed in column H and paste this to Column AL.
2. If column N displays the code 2102, 2111, 2112, 2201, 2101, 2301, 2312 then copy the text displayed in column F to column AM.

However, I'm not sure what type of formula I should use If Statement, Vlookup?

thanks,

Andy

 

[RodrigoAbreu]

Hi,
I need help to develop a VBA Stack up Formula on excel with some peculiarities.
for each "stack up" I need to repeat the data from column A until U which carries noun numeric data and stack up the data starting from column V until AG which carries numerical data from Jan to Dec (12 columns) Also, I need to do it by carrying the format of the cells, colors....
There is anyone that can help me with this VBI?
Regards

Rodrigo

 

[StephenCrump]

@Andyb2, I'm not clear why you need a macro?

Would this formula in AL2 meet your first requirement?

AL2: =IF(ISNUMBER(MATCH(N2,{2102,2111,2112,2201,2101,2301,2312},)),H2,"")

(I'm assuming you mean N contains 2012 or 2111 or 2112 or .... ?)

 

[Andyb2]

Hi Stephen,

With the formula I would have to paste this into a spreadsheet each time and filter and I want to have around 10 condition, so the 400 numbers would be office supplies for example and 500 numbers would be travel. Thats why I would like to use a formula.

I tried pasting your formula into the spreadsheet in column AL but nothing happens

 

[Andyb2]

I've used this code, which works for 2101, however when I add Or and the other numbers 2111 etc. I get the error message 'Block if without end if'?

Sub Cheesey()
    For Each r In Intersect(ActiveSheet.UsedRange, Range("A:A"))
        If r.Text = "2101" Or "2111" Or "2112" Or "2201" Or "2301" Or "2312" Then
            r.Offset(0, 1) = "Salary"  

            Else
            
        If r.Text = "3201" Or "3202" Or "3203" Or "3028" Or "3010" Then
            r.Offset(0, 1) = "Consumables"
             End If
    Next r
End Sub

 

[Fluff]

How about

Sub Cheesey()
   For Each r In Intersect(ActiveSheet.UsedRange, Range("A:A"))
      Select Case r.Text
         Case "2101", "2111", "2112", "2201", "2301", "2312"
            r.Offset(0, 1) = "Salary"
         Case "3201", "3202", "3203", "3028", "3010"
            r.Offset(0, 1) = "Consumables"
      End Select
   Next r
End Sub