VBA macro problem

L

laky131

New Member
Joined
Jan 1, 2025
Messages
1
Office Version
  1. 2021
Platform
  1. Windows
Hi, i'm I'm trying to modify the code in VBA so that it does the same as in the other cases, only one line below in my sheet "Prítomnosť1" and writes it Let Worksheets("Záznam").Cells(f, 11).Value =
I'm doing something wrong but I can't figure out what, can somebody help me ?


VBA Code: 
Sub zaznam()

Dim dat, dat1, ro As String
Dim poc As Integer
Dim d, d1 As Date

Let m = True
Let d = Worksheets("Prít. PrV").Cells(1, 31).Value
Let d1 = Worksheets("Prít. PrV").Cells(2, 31).Value
Let n = False

Let i = 4

While m = True
    Let i = i + 2
If Worksheets("Prítomnosť1").Cells(i, 1).Value = d Then
        Let r = i
    End If
If Worksheets("Prítomnosť1").Cells(6, 1).Value > d Then
        Let r = 6
    End If
If Worksheets("Prítomnosť1").Cells(i, 1).Value = d1 Then
        Let r1 = i
        Let m = False
    End If
Wend

Sheets("Záznam").Select
For i = 0 To 74
    Range(Cells(14 + i * 87, 1), Cells(40 + i * 87, 14)).Select
    Selection.ClearContents
    Range(Cells(49 + i * 87, 1), Cells(82 + i * 87, 14)).Select
    Selection.ClearContents
Next i
Range(Cells(14, 1), Cells(14, 1)).Select

Let n = False
Let ro = ""
Let ro1 = ""
Let stlpec = 0
Let poznamka = 0
Let pritomnost = 0

For i = 0 To 115
    Let f = 14 + i * 87
    For j = r To r1 Step 2
        Let s = i + 2
Let rozkaz = Trim(Worksheets("Prítomnosť1").Cells(j + 1, s).Value)
Let typ = Trim((Worksheets("Prítomnosť1").Cells(j, s).Value))
Let X = Str(Day(Worksheets("Prítomnosť1").Cells(j, 1).Value)) + "." + Str(Month(Worksheets("Prítomnosť1").Cells(j, 1).Value))
        If typ <> "" Then
If typ <> Trim((Worksheets("Prítomnosť1").Cells(j - 2, s).Value)) And poc <> 0 Then
                Let Worksheets("Záznam").Cells(f, stlpec).Value = poc
                Let Worksheets("Záznam").Cells(f, 9).Value = dat
                Let Worksheets("Záznam").Cells(f, 10).Value = dat1
                If ro1 = "" Then
                    Let Worksheets("Záznam").Cells(f, 13).Value = "PVR " + ro
                Else
                    Let Worksheets("Záznam").Cells(f, 13).Value = "PVR " + ro + ", " + ro1
                End If
                Let Worksheets("Záznam").Cells(f, 14).Value = poznamka
                Let Worksheets("Záznam").Cells(f, 12).Value = Worksheets("Prít. PrV").Range("AJ1").Value
                Let n = False
                Let dat = ""
                Let dat1 = ""
                Let poc = 0
                Let ro = ""
                Let ro1 = ""
                Let poznamka = ""
                Let stlpec = 0
                Let pritomnost = 0
                If f = 40 + i * 87 Then
                    Let f = f + 9
                Else
                    Let f = f + 1
                End If
            End If
            If rozkaz <> "" And rozkaz <> ro And poc <> 0 And stlpec <> 6 Then
                Let Worksheets("Záznam").Cells(f, stlpec).Value = poc
                Let Worksheets("Záznam").Cells(f, 9).Value = dat
                Let Worksheets("Záznam").Cells(f, 10).Value = dat1
                If ro1 = "" Then
                    Let Worksheets("Záznam").Cells(f, 13).Value = "PVR " + ro
                Else
                    Let Worksheets("Záznam").Cells(f, 13).Value = "PVR " + ro + ", " + ro1
                End If
                Let Worksheets("Záznam").Cells(f, 14).Value = poznamka
                Let Worksheets("Záznam").Cells(f, 12).Value = Worksheets("Prít. PrV").Range("AJ1").Value
                Let n = False
                Let dat = ""
                Let dat1 = ""
                Let poc = 0
                Let ro = ""
                Let ro1 = ""
                Let poznamka = ""
                Let stlpec = 0
                Let pritomnost = 0
                If f = 40 + i * 87 Then
                    Let f = f + 9
                Else
                    Let f = f + 1
                End If
            Else
                If stlpec = 6 Then
                    Let ro1 = rozkaz
                End If
            End If
            Select Case typ
            Case "P100"
                Let stlpec = 1
                Let poznamka = "P100"
                Let pritomnost = 1
            Case "P100 0,5"
                Let stlpec = 1
                Let poznamka = "P100-0,5 dňa"
                Let pritomnost = 1
            Case "P101 0,5"
                Let stlpec = 2
                Let poznamka = "P101-0,5 dňa"
                Let pritomnost = 1
            Case "P100s"
                Let stlpec = 1
                Let poznamka = "P100-minuloročná"
                Let pritomnost = 1
            Case "P101"
                Let stlpec = 2
                Let poznamka = "P101"
                Let pritomnost = 1
             Case "P140"
                Let stlpec = 5
                Let poznamka = "P140"
                Let pritomnost = 1
            Case "P141"
                Let stlpec = 5
                Let poznamka = "P141"
                Let pritomnost = 1
            Case "P450"
                Let stlpec = 3
                Let poznamka = "P450"
                Let pritomnost = 1
            Case "P116", "P112 0,25", "P112 0,5", "P112 0,75", "P112 1", "P112 1,25", "P112 1,5", "P112 1,75", "P112 2", "P112 2,25", "P112 2,5", "P112 2,75", "P112 3", "P112 3,25", "P112 3,5", "P112 3,75", "P112 4", "P112 4,25", "P112 4,5", "P112 4,75", "P112 5", "P112 5,25", "P112 5,5", "P112 5,75", "P112 6", "P112 6,25", "P112 6,5", "P112 6,75", "P112 7", "P112 7,25", "P112 7,5", "P112 7,75", "P112 8", "P113 0,25", "P113 0,5", "P113 0,75", "P113 1", "P113 1,25", "P113 1,5", "P113 1,75", "P113 2", "P113 2,25", "P113 2,5", "P113 2,75", "P113 3", "P113 3,25", "P113 3,5", "P113 3,75", "P113 4", "P113 4,25", "P113 4,5", "P113 4,75", "P113 5", "P113 5,25", "P113 5,5", "P113 5,75", "P113 6", "P113 6,25", "P113 6,5", "P113 6,75", "P113 7", "P113 7,25", "P113 7,5", "P113 7,75", "P113 8", "P104", "P104k", "P130", "P130š"
                Let stlpec = 6
                If typ = "P116" Then
                    Let poznamka = "P116"
                    End If
                 If typ = "P112 0,25" Then
                    Let poznamka = "P112-0,25 hod."
                End If
                 If typ = "P112 0,5" Then
                    Let poznamka = "P112-0,5 hod."
                    End If
                 If typ = "P112 0,75" Then
                    Let poznamka = "P112-0,75 hod."
                End If
                If typ = "P112 1" Then
                    Let poznamka = "P112-1 hod."
                    End If
                If typ = "P112 1,25" Then
                    Let poznamka = "P112-1,25 hod."
                End If
                If typ = "P112 1,5" Then
                    Let poznamka = "P112-1,5 hod."
                    End If
                If typ = "P112 1,75" Then
                    Let poznamka = "P112-1,75 hod."
                End If
                If typ = "P112 2" Then
                    Let poznamka = "P 112-2 hod."
                    End If
                If typ = "P112 2,25" Then
                    Let poznamka = "P112-2,25 hod."
                End If
                If typ = "P112 2,5" Then
                    Let poznamka = "P112-2,5 hod."
                    End If
                If typ = "P112 2,75" Then
                    Let poznamka = "P112-2,75 hod."
                End If
                If typ = "P112 3" Then
                   Let poznamka = "P112-3 hod."
                    End If
                If typ = "P112 3,25" Then
                    Let poznamka = "P112-3,25 hod."
                End If
                If typ = "P112 3,5" Then
                    Let poznamka = "P112-3,5 hod."
                    End If
                If typ = "P112 3,75" Then
                    Let poznamka = "P112-3,75 hod."
                End If
                If typ = "P112 4" Then
                    Let poznamka = "P112-4 hod."
                End If
                If typ = "P112 4,25" Then
                    Let poznamka = "P112-4,25 hod."
                    End If
                If typ = "P112 4,5" Then
                    Let poznamka = "P112-4,5 hod."
                    End If
                If typ = "P112 4,75" Then
                    Let poznamka = "P112-4,75 hod."
                End If
                If typ = "P112 5" Then
                    Let poznamka = "P112-5 hod."
                    End If
                If typ = "P112 5,25" Then
                    Let poznamka = "P112-5,25 hod."
                    End If
                If typ = "P112 5,5" Then
                    Let poznamka = "P112-5,5 hod."
                End If
                If typ = "P112 5,75" Then
                    Let poznamka = "P112-5,75 hod."
                End If
                If typ = "P112 6" Then
                    Let poznamka = "P112-6 hod."
                End If
                If typ = "P112 6,25" Then
                    Let poznamka = "P112-6,25 hod."
                     End If
                If typ = "P112 6,5" Then
                    Let poznamka = "P112-6,5 hod."
                     End If
                If typ = "P112 6,75" Then
                    Let poznamka = "P112-6,75 hod."
                End If
                If typ = "P112 7" Then
                    Let poznamka = "P112-7 hod."
                End If
                If typ = "P112 7,25" Then
                    Let poznamka = "P112-7,25 hod."
                    End If
                If typ = "P112 7,5" Then
                    Let poznamka = "P112-7,5 hod."
                    End If
                If typ = "P112 7,75" Then
                    Let poznamka = "P112-7,75 hod."
                End If
                If typ = "P112 8" Then
                    Let poznamka = "P112-8 hod."
                End If
                If typ = "P113 0,25" Then
                    Let poznamka = "P113-0,25 hod."
                   End If
                If typ = "P113 0,5" Then
                    Let poznamka = "P113-0,5 hod."
                    End If
                If typ = "P113 0,75" Then
                    Let poznamka = "P113-0,75 hod."
                End If
                If typ = "P113 1" Then
                    Let poznamka = "P113-1 hod."
                End If
                If typ = "P113 1,25" Then
                    Let poznamka = "P113-1,25 hod."
                    End If
               If typ = "P113 1,5" Then
                    Let poznamka = "P113-1,5 hod."
                    End If
                If typ = "P113 1,75" Then
                    Let poznamka = "P113-1,75 hod."
                End If
                If typ = "P113 2" Then
                    Let poznamka = "P113-2 hod."
                End If
                If typ = "P113 2,25" Then
                    Let poznamka = "P113-2,25 hod."
                     End If
                If typ = "P113 2,5" Then
                    Let poznamka = "P113-2,5 hod."
                     End If
                If typ = "P113 2,75" Then
                    Let poznamka = "P113-2,75 hod."
                End If
                If typ = "P113 3" Then
                    Let poznamka = "P113-3 hod."
                End If
                If typ = "P113 3,25" Then
                    Let poznamka = "P113-3,25 hod."
                    End If
                If typ = "P113 3,5" Then
                    Let poznamka = "P113-3,5 hod."
                    End If
                If typ = "P113 3,75" Then
                    Let poznamka = "P113-3,75 hod."
                End If
                If typ = "P113 4" Then
                    Let poznamka = "P113-4 hod."
                End If
                If typ = "P113 4,25" Then
                    Let poznamka = "P113-4,25 hod."
                    End If
                If typ = "P113 4,5" Then
                    Let poznamka = "P113-4,5 hod."
                    End If
                If typ = "P113 4,75" Then
                    Let poznamka = "P113-4,75 hod."
                End If
                If typ = "P113 5" Then
                    Let poznamka = "P113-5 hod."
                End If
                If typ = "P113 5,25" Then
                    Let poznamka = "P113-5,25 hod."
                     End If
                If typ = "P113 5,5" Then
                    Let poznamka = "P113-5,5 hod."
                     End If
                If typ = "P113 5,75" Then
                    Let poznamka = "P113-5,75 hod."
                End If
                If typ = "P113 6" Then
                    Let poznamka = "P113-6 hod."
                End If
                 If typ = "P113 6,25" Then
                    Let poznamka = "P113-6,25 hod."
                    End If
                 If typ = "P113 6,5" Then
                    Let poznamka = "P113-6,5 hod."
                    End If
                 If typ = "P113 6,75" Then
                    Let poznamka = "P113-6,75 hod."
                End If
                 If typ = "P113 7" Then
                    Let poznamka = "P113-7 hod."
                End If
                 If typ = "P113 7,25" Then
                    Let poznamka = "P113-7,25 hod."
                    End If
                 If typ = "P113 7,5" Then
                    Let poznamka = "P113-7,5 hod."
                    End If
                 If typ = "P113 7,75" Then
                    Let poznamka = "P113-7,75 hod."
                End If
                If typ = "P113 8" Then
                    Let poznamka = "P113-8 hod."
                End If
                If typ = "P104" Then
                    Let poznamka = "P104"
                End If
                If typ = "P110" Then
                    Let poznamka = "P110"
                End If
                If typ = "P130" Then
                    Let poznamka = "P130"
                End If
                 If typ = "P160" Then
                    Let poznamka = "P160"
                End If
                Let pritomnost = 1
          Case "P199 8"
                Let stlpec = 6
                If typ = "P199 8" Then
                    Let poznamka = "P199-8 hod."
                End If
                If typ = "P199 4" Then
                    Let poznamka = "P199-4 hod."
                End If
          Case "P400"
                Let stlpec = 7
                Let poznamka = "P400"
                Let pritomnost = 1
            Case "P401"
                Let stlpec = 7
                Let poznamka = "P401"
                Let pritomnost = 1
            Case "P405"
                Let stlpec = 7
                Let poznamka = "P405"
                Let pritomnost = 1
            Case "A"
                Let stlpec = 8
                Let poznamka = ""
                Let pritomnost = 1
            Case "P115"
                Let stlpec = 6
                Let poznamka = "P115"
                Let pritomnost = 1
            Case "P199 4"
                Let stlpec = 6
                Let poznamka = "P199-4 hod."
                Let pritomnost = 1
            End Select
            If pritomnost = 1 Then
                If dat = "" Then
Let dat = Worksheets("Prítomnosť1").Cells(j, 1).Value
Let dat1 = Worksheets("Prítomnosť1").Cells(j, 1).Value
                End If
                If ro = "" Then
Let ro = Trim(Worksheets("Prítomnosť1").Cells(j + 1, s).Value)
                End If
Let x5 = Str(Day(Worksheets("Prítomnosť1").Cells(j, 1).Value)) + "." + Str(Month(Worksheets("Prítomnosť1").Cells(j, 1).Value))
If Trim(Worksheets("Prítomnosť1").Cells(j, s).Value) = "P104" Then
If ((Weekday(Worksheets("Prítomnosť1").Cells(j, 1).Value) <> 1 And Weekday(Worksheets("Prítomnosť1").Cells(j, 1).Value) <> 7) And (x5 <> " 1. 1" And x5 <> " 6. 1" And x5 <> " 1. 5" And x5 <> " 8. 5" And x5 <> " 5. 7" And x5 <> " 29. 8" And x5 <> " 1. 9" And x5 <> " 15. 9" And x5 <> " 24. 12" And x5 <> " 25. 12" And x5 <> " 26. 12" And x5 <> " 1. 11" And x5 <> " 17. 11")) Then
                        Let poc = poc + 1
                    End If
If Trim(Worksheets("Prítomnosť1").Cells(j, s).Value) = "P104k" Then
If ((Weekday(Worksheets("Prítomnosť1").Cells(j, 1).Value) <> 1 And Weekday(Worksheets("Prítomnosť1").Cells(j, 1).Value) <> 7) And (x5 <> " 1. 1" And x5 <> " 6. 1" And x5 <> " 1. 5" And x5 <> " 8. 5" And x5 <> " 5. 7" And x5 <> " 29. 8" And x5 <> " 1. 9" And x5 <> " 15. 9" And x5 <> " 24. 12" And x5 <> " 25. 12" And x5 <> " 26. 12" And x5 <> " 1. 11" And x5 <> " 17. 11")) Then
                        Let poc = poc + 1
                    End If
                    End If
                    Else
                        Let poc = poc + 1
                    End If
                If dat <> "" Then
Let dat1 = Worksheets("Prítomnosť1").Cells(j, 1).Value
                        Let n = True
                    End If
                End If
        Else
            If poc <> 0 Then
                Let Worksheets("Záznam").Cells(f, stlpec).Value = poc
                Let Worksheets("Záznam").Cells(f, 9).Value = dat
                Let Worksheets("Záznam").Cells(f, 10).Value = dat1
                If ro1 = "" Then
                    Let Worksheets("Záznam").Cells(f, 13).Value = "PVR " + ro
                Else
                    Let Worksheets("Záznam").Cells(f, 13).Value = "PVR " + ro + ", " + ro1
                End If
                Let Worksheets("Záznam").Cells(f, 14).Value = poznamka
                Let Worksheets("Záznam").Cells(f, 12).Value = Worksheets("Prít. PrV").Range("AJ1").Value
                Let n = False
                Let dat = ""
                Let dat1 = ""
                Let poc = 0
                Let ro = ""
                Let ro1 = ""
                Let poznamka = ""
                Let stlpec = 0
                Let pritomnost = 0
                If f = 40 + i * 87 Then
                    Let f = f + 9
                Else
                    Let f = f + 1
                End If
            End If
        End If
    Next j
    If poc <> 0 Then
        Let Worksheets("Záznam").Cells(f, stlpec).Value = poc
        Let Worksheets("Záznam").Cells(f, 9).Value = dat
        Let Worksheets("Záznam").Cells(f, 10).Value = dat1
        If ro1 = "" Then
            Let Worksheets("Záznam").Cells(f, 13).Value = "PVR " + ro
        Else
            Let Worksheets("Záznam").Cells(f, 13).Value = "PVR " + ro + ", " + ro1
        End If
        Let Worksheets("Záznam").Cells(f, 14).Value = poznamka
        Let Worksheets("Záznam").Cells(f, 12).Value = Worksheets("Prít. PrV").Range("AJ1").Value
        Let n = False
        Let dat = ""
        Let dat1 = ""
        Let poc = 0
        Let ro = ""
        Let ro1 = ""
        Let poznamka = ""
        Let stlpec = 0
        Let pritomnost = 0
        If f = 40 + i * 87 Then
            Let f = f + 9
        Else
            Let f = f + 1
        End If
    End If
Next i
      
End Sub
 
Last edited by a moderator:

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number
Sort by date Sort by votes
Before the loop For i = 0 To 115 you have an assignment to the variable stlpec = 0. Then in the loop you refer to the cell: Worksheets(“Záznam”).Cells(f, stlpec).Value = poc. There is no column “zero” in the sheet, hence the error.

Artik
 
Upvote 0
Welcome to the Board!

Two other notes, as it pertains to programming in VBA in Excel. Neither of these two things will cause errors, per se, but they are either unnecessary or not doing what you think.

1. When declaring variables in VBA, if you do not declare each variable explicitly, those not declared explicitly will be set to the "Variant" data type.
So lines like this:
Rich (BB code): 
Dim dat, dat1, ro As String
...
Dim d, d1 As Date
are not doing what you think.
This will declare "ro As String" and "d1 as Date", but "dat", "dat1", and "d" will all be declared as Variant.

In order to do what you intended, you would need to write those like this:
Rich (BB code): 
Dim dat As String, dat1 As String, ro As String
...
Dim d as Date, d1 As Date
or like this:
Rich (BB code): 
Dim dat As String
Dim dat1 As String
Dim ro As String
...
Dim d as Date
Dim d1 As Date

2. When setting non-object variables, it is not necessary to use "Let" in VBA.
So all of your lines that look like this:
VBA Code: 
Let i = i + 2
can drop the "Let" and just be written like this:
VBA Code: 
i = i + 2
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Solved
VBA Code Error Unable to get the Match property of the WorksheetFunction class
Replies
8
Views
468
Jiraya_00
J
R
  • Solved
VBA - Combine worksheets from multiple workbooks
Replies
5
Views
372
rachel06
R
R
  • Question Question
Highlighting cells based on criteria
Replies
0
Views
452
rahildhody
R
S
  • Question Question
Adding a new field to the results of my "list the folders inside a specific folder" code
Replies
2
Views
406
John_w
J
S
  • Question Question
VBA Copy/Paste from different workbooks
Replies
1
Views
391
NdNoviceHlp
NdNoviceHlp

Forum statistics

Threads
1,225,217
Messages
6,183,635
Members
453,177
Latest member
GregL65

Share this page

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