Write Variable To Cell In VBA

Question

I am needing to follow the protocol below:
I am scanning Sheet1 and for each unique empName on that worksheet selecting the individual empName worksheet.
on the individual empName worksheet capturing the value in the last cell in column O
Storing the value in variable tper (it's a percentage)
Selecting sheet1
Writing a header to column N1
Selecting the 1st empty cell in column N (excluding the header)
write the value of tper to the selected cell in column N
Repeat until all empNames have been processed from Sheet1

My syntax seems to execute as it should up until this line lr1 = Cells(Rows.Count, 13).End(xlUp).Row where it throws an error of

error invalid qualifier

What do I need to re-write in order for this to follow the protocol outlined above?

Function Test()
Dim lr As Long, i As Long, lr1 As Long, i1 As Long
Dim WS As Worksheet, empName As String, tper As Variant

Set WS = ActiveSheet
lr = Cells(Rows.Count, 2).End(xlUp).Row
For i = 2 To lr
  empName = WS.Cells(i, 2).Value
  Sheets(empName).Select
  tper = "=LOOKUP(2,1/(O:O<>""),O:O)"
  Sheets("Sheet1").Select
  Range("N1").FormulaR1C1 = "Percent"
  lr1 = Cells(Rows.Count, 13).End(xlUp).Row
  For i1 = 2 To lr1
    lr1.Cells.FormulaR1C1 = tper
  Next i1
Next i
End Function

You declare `lr1 as Long`,So `lr1.Cell.FormulaR1C1 = tper` is wrong! — Andy, Dec 08 '16 at 00:55
You need to fully qualify all of your calls to `Cells` and `Rows`. You also don't need to `.Select` ***anything*** in the loop. You're just switching back and forth between `empName` and `"Sheet1"` without ever using the `empName` selections. [Read this entire page](http://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba-macros) - it will help more than any single answer you'll get to this question. — Comintern, Dec 08 '16 at 01:02
I would also declare a variable for Sheet1 and assign `set Sheet1 = Sheets("Sheet1")`. That way you can reference that sheet when you need to without having to Select the sheet. which is basically like you phisically clicking the sheet1 tab — Nathan Fisher, Dec 08 '16 at 01:03
@Andy - how would I remedy? I see the issue now, but no clue on how to fix. — StarsFlyFree FromCozyNights, Dec 08 '16 at 01:37
@NathanFisher - do it like so? Dim sheet1 As Worksheet Set sheet1 = Worksheets("Sheet1") Set ws = ActiveSheet sheet1.Activate — StarsFlyFree FromCozyNights, Dec 08 '16 at 01:39
The second for loop will fill all rows in columns N (if written correctly) with tper, but you want only the last cell to have that value right? "Selecting the 1st empty cell in column N (excluding the header) write the value of tper to the selected cell in column N" You dont need that for loop. also Column N is `Cells(Rows.count,14)` — nightcrawler23, Dec 08 '16 at 01:39
@StarsFlyFreeFromCozyNights Maybe is `Cells(il, 13).FormulaR1C1 = your formula`. — Andy, Dec 08 '16 at 01:46
@nightcrawler23 - I want the 1st empty cell to have the value of tper. I added a loop because tper will vary depending on the empName worksheet. So each time a new empName worksheet is Activated I need to find the next empty row in column N to write the tper value to. — StarsFlyFree FromCozyNights, Dec 08 '16 at 01:47

score 2 · Accepted Answer · answered Dec 08 '16 at 01:55

I have attempted to modify your code. see if this works. Since you want to loop through all employees in Sheet1, which is being taken care of by the first for loop, I got rid of the second loop.

Sub Test()
    Dim empName As String, tper As Variant

    Dim WS As Worksheet, empSheet As Worksheet
    Set WS = Sheets("Sheet1")

    Dim lr As Long
    lr = WS.Cells(Rows.Count, 2).End(xlUp).Row

    Dim i As Long
    For i = 2 To lr
        'scanning Sheet1 and for each unique empName
        empName = WS.Cells(i, 2).Value

        'selecting the individual empName worksheet
        'just set to variable. no need to select
        Set empSheet = Sheets(empName)

        'on the individual empName worksheet capturing the value in the last cell in column O
        'Storing the value in variable tper (it's a percentage)
        tper = empSheet.Range("O" & Rows.Count).End(xlUp).Value

        'Selecting Sheet1
        'Writing a header to column N1
        WS.Range("N1").FormulaR1C1 = "Percent"

        'Selecting the 1st empty cell in column N (excluding the header)
        WS.Range("N" & Rows.Count).End(xlUp).Offset(1, 0) = tper

        'Repeat until all empNames have been processed from Sheet1
        '  next i will reapeat for the next employee in sheet 1
    Next i
End Sub

You also mentioned

for each unique empName

The code does not check that.

Write Variable To Cell In VBA

1 Answers1