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.
I'm trying to get different values:
When formula see "A", I get below values
When formula see "B" or "C" then
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"
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