[VBA] Formula IF with variables

BluEEyE86

New Member
Joined
May 25, 2021
Messages
34
Office Version
  1. 365
Platform
  1. Windows
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.

Excel Formula:
=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
Excel Formula:
=IF(Q2="A";O:O;IF(Q2="B";A;IF(Q2="C";B;"U")))

When formula see "B" or "C" then
Excel Formula:
=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"

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
 

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.

offthelip

Well-known Member
Joined
Dec 23, 2017
Messages
2,761
Office Version
  1. 2010
Platform
  1. Windows
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:
VBA Code:
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
 
Solution

BluEEyE86

New Member
Joined
May 25, 2021
Messages
34
Office Version
  1. 365
Platform
  1. Windows
Thank you, it works perfectly now.

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

Forum statistics

Threads
1,181,947
Messages
5,932,918
Members
436,869
Latest member
ABGTH

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top