Need Help ASAP Issue with Subs

agustinodavalos

New Member
Joined
Aug 17, 2015
Messages
3
I am new to VBA and I'm teaching myself how to use it.

In this case there are two sheets (Sheet 2: "HC" and Sheet 3: "GC")
I am trying to create a few functions/subs (not really sure of the difference) that I can reference that will return values that I have them calculate
All my work is done within Sheet 3 in VBA

So I essentially have three sections

Sub fnc1()

End Sub

...

Sub fncN()

End Sub


Sub Worksheet_Activate()

End Sub


Sub Worksheet_Change(ByVal Target As Range)


End Sub


Now say one of the functions I want to run is this
Sub HCRRange()
' Obtain the last Row number on the Hard Coded Collection Sheet
a = 2
flag = True
While flag = True
If Sheets("Hard Coded Collection").Cells(a, 1) <> "" Then
a = a + 1
Else
flag = False
End If
Wend
HCRRange = a - 1
End Sub


And now I want to access this in my Sheet Change function

Sub Worksheet_Change(ByVal Target As Range)
Dim HCR As Integer
HCR = HCRRange()

End Sub

But i get "compiled error expected function or variable"
What am I doing wrong :(
 

Some videos you may like

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

V_Malkoti

Well-known Member
Joined
Jun 10, 2015
Messages
898
HCRange must be declared as a function since you expect it to return a value. Change your code to:

Code:
Function HCRRange()
' Obtain the last Row number on the Hard Coded Collection Sheet
    HCRRange = Sheets("Hard Coded Collection").Cells(Rows.Count, 1).End(xlUp).Row
End Function
I changed the code to get last row in a column inside HCRRange as this is concise.
 

mole999

Moderator
Joined
Oct 23, 2004
Messages
10,109
Office Version
2019, 2016, 2013
Platform
Windows
add Option Explicit above the first sub, when you compile you will see what isn't defined
 

Watch MrExcel Video

Forum statistics

Threads
1,090,412
Messages
5,414,261
Members
403,522
Latest member
Abel_excel

This Week's Hot Topics

Top