[VBA] Formula IF with variables

[BluEEyE86]

Hello Experts,
My aim is to get below formula through VBA code where accordingly values C, A & B are ratating with next weeks. For example, this week A is C, B is A & C is B but in next week A will be A, B will be B and C will be C,. Further week, A will be B, B will be C and C will be A. Then changes starting again from beggining.

=IF(Q2="A";"C";IF(Q2="B";"A";IF(Q2="C";"B";"U")))

I'm trying to get different values:

When formula see "A", I get below values

=IF(Q2="A";O:O;IF(Q2="B";A;IF(Q2="C";B;"U")))

When formula see "B" or "C" then

=IF(Q138="A";O:O;JIF(Q138="B";A;IF(Q138="C";B;"U")))

I know that I have missing "" for B and C & excel doesn't recognize this as String characters but I have no idea why it is "O:O" when "A" appears.

Can you help to improve this code ?

My VBA code"

Dim Shift1 As String
            Dim Shift2 As String
            Dim Shift3 As String


            Shift1 = InputBox("Jaka zmiana pracuje na rannej zmianie ?")
            Shift2 = InputBox("Jaka zmiana pracuje na popołudniowej zmianie ?")
            Shift3 = InputBox("Jaka zmiana pracuje na noccnej zmianie ?")
          
          
            Range("A1").Select
          
            Columns("O:O").Select
            Selection.Cut
            Columns("Q:Q").Select
            ActiveSheet.Paste
            Range("Q1").Select
            ActiveCell.FormulaR1C1 = "stare"
            Range("O1").Select
            ActiveCell.FormulaR1C1 = "shift"
            Range("O2").Select
            ActiveCell.FormulaR1C1 = _
                "=IF(RC[2]=""A""," & Shift1 & ",IF(RC[2]=""B""," & Shift2 & ",IF(RC[2]=""C""," & Shift3 & ",""U"")))"
            Range("O2").Select
            Selection.AutoFill Destination:=Range("O2:O" & Range("A2").End(xlDown).Row), Type:=xlFillDefault
            Range("O2:O" & Range("A2").End(xlDown).Row).Select
            Selection.Copy
            Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
                :=False, Transpose:=False

 

[offthelip]

Writing an if statement to a workhseet and then overwriting it with the values is a very inefficient and slow way of doing what you want. It is very easy to use the logic available in VBA to do the whole task in one go. This sub will copy the values to column Q and then invcrement the values in colum O round the cyc le you want:

Sub test()
lastrow = Cells(Rows.Count, "O").End(xlUp).Row
inarr = Range(Cells(1, 15), Cells(lastrow, 15))  ' column 15 is column O
inarr(1, 1) = "stare"
Range(Cells(1, 17), Cells(lastrow, 17)) = inarr
inarr(1, 1) = "shift2"
For i = 2 To lastrow
  Select Case inarr(i, 1)
  
  Case "A"
   inarr(i, 1) = "B"
  Case "B"
   inarr(i, 1) = "C"
  Case "C"
   inarr(i, 1) = "A"
End Select
Next i
Range(Cells(1, 15), Cells(lastrow, 15)) = inarr 'write the data back to wokhseet


  
End Sub

 

[BluEEyE86]

Thank you, it works perfectly now.

I see, still a lot of work in front of me.