Changing Value of a Cell Excel VBA Macro

[DJL_DASH]

Hello,
I am working on a code that writes labels to each row on a worksheet. The worksheet is comprised of a header row, which has a header for each category:

|ID|Category|Sub-Category|Metric|Sub-Metric|Unit|Priority|Existing Reference|Comments|Data Link|


Last row of the sheet can very depending on how much data is entered. Therefore, I want to find the number of the last filled out row on the sheet. This is the job of the first function:

 Public Sub TestIdLabel()
    Total = ActiveSheet.Range("A9999").End(xlUp).Rows.Count
    IdLabel (Total)
End Sub

I then call IdLabel(). The labels will be written in the first column "ID" I am trying to reference a cell in the first column to change the value of the cell depending on the row number.

Public Sub IdLabel(ByRef totCount As Long)
    Dim rowCount, subRowCount, idCount, letterCount, marker As Integer
    Dim letString, IdLabel, curCell As String
    Dim rowSelect As Long

    'Predefine necessary variables
    idCount = 1
    letterCount = 0
    rowCount = 2
    letString = "ABCDEFGHIJKLMNOPQRSTUVWXYZ"

    'Determine Name of label
    IdLabel = Application.InputBox("Please specify ID Label.", "ID LABEL") + "-"
    'Loops through each cell, looks at column L, if marker is present,
    'id labels loop through letterCount to find appropriate ID label
    Do
        If ActiveSheet.Cells(rowCount, "L") = ActiveSheet.Cells(rowCount + 1, "L") Then
            'Labels sub-metric "idLabel-rowCount"A,B,C,etc."" for as long as marker is present in column L
            Do
'ERROR OCCURS HERE                ActiveSheet.Cells("A" & rowCount).Value = IdLabel & rowCount.String & letString(letterCount)  
                '^^^^Eventually change so column is specified
                letterCount = letterCount + 1
                rowCount = rowCount + 1
            Loop While ActiveSheet.Cells(rowCount, "L") <> Empty _
            And ActiveSheet.Cells(rowCount, "L") = ActiveSheet.Cells(rowCount + 1, "L")
        Else:
            ActiveSheet.Cells(rowCount, "A") = IdLabel & rowCount.String
        End If
        
        letterCount = 1
        rowCount = rowCount + 1
        
    Loop Until rowCount = totCount

End Sub

Yet I keep getting the following error: 'object required' In addition, up in the first function, "Total" returns as 1, which is certainly not true considering I have more than 1 row of data entered in my sheet. Could anyone clarify what is happening? I am new to VBA, so I definitely have not mastered the language semantics.

 

[trunten]

I think you were trying to use your string object letString as an array. I've changed that for you. Hope it all works.

Public Sub TestIdLabel()
    Total = ActiveSheet.UsedRange.Rows.Count
    IdLabel (Total)
End Sub

Public Sub IdLabel(ByRef totCount As Long)
    Dim rowCount As Integer, subRowCount As Integer
    Dim idCount As Integer, letterCount As Integer, marker As Integer
    Dim letString As String, IdLabel As String, curCell As String
    Dim rowSelect As Long

    'Predefine necessary variables
    idCount = 1
    letterCount = 0
    rowCount = 2
    letString = "ABCDEFGHIJKLMNOPQRSTUVWXYZ"

    'Determine Name of label
    IdLabel = Application.InputBox("Please specify ID Label.", "ID LABEL") + "-"
    'Loops through each cell, looks at column L, if marker is present,
    'id labels loop through letterCount to find appropriate ID label
    Do
        If ActiveSheet.Cells(rowCount, "L") = ActiveSheet.Cells(rowCount + 1, "L") Then
            'Labels sub-metric "idLabel-rowCount"A,B,C,etc."" for as long as marker is present in column L
            Do
                ActiveSheet.Cells("A" & rowCount).Value = IdLabel & rowCount & Mid(letterCount, 1)
                '^^^^Eventually change so column is specified
                letterCount = letterCount + 1
                rowCount = rowCount + 1
            Loop While ActiveSheet.Cells(rowCount, "L") <> Empty _
            And ActiveSheet.Cells(rowCount, "L") = ActiveSheet.Cells(rowCount + 1, "L")
        Else:
            ActiveSheet.Cells(rowCount, "A") = IdLabel & rowCount
        End If
        
        letterCount = 1
        rowCount = rowCount + 1
        
    Loop Until rowCount = totCount

End Sub

 

[DJL_DASH]

Hmmm...now I get the following error at the same line as before:

Run-time error '1004':

Application-defined or object-defined error

It DOES seem to be recognizing my sheet this time around, because Total = 14, which is how many rows I have occupied. So that problem seems to be fixed. But there is still an error on:

  ActiveSheet.Cells("A" & rowCount).Value = IdLabel & rowCount & Mid(letterCount, 1)

Anyone have any other suggestions?

Referring to a cell is very frustrating because it seems as though there are a number of ways you could do it. Either by referencing the workbook by name or using .range or .cells command, but I never know which way is appropriate in a given instance... If someone could clarify this for me that would be helpful.