Compiler Syntax Error

[dmqueen]

I am trying to reposition the cursor to the correct row and column, and keep receiving compiler syntax errors.

<code>
'Written By: Dawn Queen
'For: Tools For Bending
'On: August 15, 2014
'Contact: dmcole@mail.com
'Purpose: Guide Input for Engineers and track Parts Created for Clients
'Converted Historical Lotus file and rewrote and updated scripting

Option Explicit
Private Sub Workbook_Activate()
Application.MoveAfterReturnDirection = xlToRight
End Sub

Public Sub fInputPart()
'col A
'add a new row
'get last PartNo.
'add 1
'generate new part no.
'CurrentWorksheet.Activate()
ActiveSheet.Range("A1").Select
'goto the top, and cycle down each line until you find the top of the entries, "="
'goto the top entry ready to insert the new entry
While ActiveCell.Value <> "="
ActiveCell.Offset(1, 0).Activate
Wend
'OK, we found the top of the entries
'Don't overwrite the =, go below
ActiveCell.Offset(1, 0).Activate
'insert the new entry row
Selection.EntireRow.Insert , CopyOrigin:=xlFormatFromLeftOrAbove
'note where we are
Dim NewRowNum As Integer
NewRowNum = ActiveCell.Row
'Application defined or object defined error 1004
'Problem with line below!!
'Range("A" & ActiveCell.Row).Select
'Goto last part number entry
'Get the last part number here!!
Application.ActiveCell.Offset(1, 0).Activate
Dim LastPartNO As Integer
'Put the new part number here!!
Dim NewPartNo As String
NewPartNo = FgenerateNextPartNumber(Application.ActiveCell.Value)
Range("A" & NewRowNum).Value = NewPartNo
'debugging
'Call MsgBox("NewPartNo is: " & NewPartNo)
'Call MsgBox("Row Num 2 insert Part Num @ is: " & NewRowNum)
'this next line is VITAL or it will hang INDEFINETLY!
Application.Range("A" & NewRowNum).Value = NewPartNo
'go to next col
'col B
'verify entry was made
'go to next col
'while there are columns for data entry (has column title) verify entry was made in last column,
'error msg if not,
'go to next column if made
'Part Number in- goto next column and start loop
'y r we dropping a row!?
'If we've moved off target row, move back to it!
ActiveCell.Offset(0, 1).Activate
'THIS IS WHAT I TRIED BEFORE.
If ActiveCell.Row <> NewRowNum Then
'Application.ActiveCell.Row() = NewRowNum
'THIS IS WHERE I'm RECEIVING THE ERROR
Application.ActiveCellColumn & NewRowNum.Activate()
End If

While (NewRowNum & Application.ActiveCell.Column <> "")
'When OK key hit, ck that value entered and goto next column
'On KeypressByVal(KeyAscii As MSForms.ReturnInteger)
'If KeyAscii = 13 Then
'while there are columns left for entry: there is a column header
'go to the next column for entry that is active and has a width not equal to 1
'verify entry was made in last column: holler and stop if not: continue if made
'blank allowed only for "NOTES" column!
'/******************************************************************************
'REVISE THIS TO SEARCH FROM TOP ROW, DON'T ASSUME IT'LL BE ON ROW 6!
If ActiveCell.Offset(0, 1) = Null Then
Range(ActiveCell.Column & "6").Select
If (ActiveCell.Value()) <> "NOTES" Then

Call MsgBox("Please enter/select a value in the previous column!", vbCritical, Application.Name)

Else
'if inactive(width=1, jump it, else goto next column and stop for entry
If ActiveCell.Offset(0, 1).ColumnWidth = 1 Then
ActiveCell.Offset(0, 2).Activate
Else: ActiveCell.Offset(0, 1).Activate
End If
End If
End If
'End If
Wend
Call MsgBox("Entry Complete, thank you! Don't forget to save when done! ", vbInformation, Application.Name)
End Sub
Public Function FgenerateNextPartNumber(LastPartIn As String) As String
Dim LastPartNO As String
LastPartNO = LastPartIn
'LastPartNo = ActiveCell.Value
Dim NewStrPartNo As String
Dim strseparator As String
Dim strPartNo As String
Dim strLastPartNo As String

strPartNo = ActiveSheet.Name()
Dim strTrimWksName As String
strTrimWksName = Left(strPartNo, 3)
strPartNo = strTrimWksName
Dim strSeperator As String

strLastPartNo = Right(LastPartIn, 4)

Dim strNewSeqPartNo As String
Dim intNewSeqNo As Integer

Dim intLastSeqNo As Integer
Dim lastseqNo As Integer

'debugging
'Call MsgBox("strLastPartNo: " & strLastPartNo)
Dim tempConvert As Integer

tempConvert = CInt(strLastPartNo)

intLastSeqNo = tempConvert
intNewSeqNo = intLastSeqNo + 1
'handle special case separators HERE!
'debugging
'Call MsgBox("Generating Part Number! Last Part No. was " & intLastSeqNo)
If strPartNo = "180" Or strPartNo = "300" Or strPartNo = "310" Or strPartNo = "320" Or strPartNo = "330" Or strPartNo = "970" Or strPartNo = "681" Or strPartNo = "981" Then
strseparator = "-1-"

Else: strseparator = "-0-"
End If
'put in return stmt 4 compiler
NewStrPartNo = strPartNo + strseparator + CStr(intNewSeqNo)
'return statement
FgenerateNextPartNumber = NewStrPartNo

End Function
</code>
I've included all the code 2 b sure.
I added a watch on NewRowNum and ActiveCellColumn and the values are correct going into this line.

 

[Norie]

Where in all that code do you get the error?

 

[dmqueen]

On the line
Application.ActiveCellColumn & NewRowNum.Activate()
I put a comment above it, 'THIS IS WHERE I GET THE ERROR

 

[Rick Rothstein]

On the line
Application.ActiveCellColumn & NewRowNum.Activate()
I put a comment above it, 'THIS IS WHERE I GET THE ERROR

Well, there is definitely a problem with that line... there is no ActiveCellColumn property for the Application object. You could get that value by using a dot in front of the keyword "Column" (like this... Application.ActiveCell.Column) which I guess is what you mean to type, but putting the dot in won't make the code correct because you would then be attempting to concatenate the number of the column with the Activate method (an action, not a return value) for NewRowNum which I think is a number (and, hence, does not even have an Activate method). I cannot offer you an alternate code to use because what you are actually trying to do with that line is not at all clear to me. If you can explain in words what you were trying to do at that location in your code, someone here will attempt to offer you alternative coding for you to use.

 

[dmqueen]

Thanks I see that. With the proper . in, the ampersand is now highlighted and compiler says expected expression.

 

[dmqueen]

Thanks, I see that. With Application.ActiveCell.Column I get an "expression expected compiler error at the Ampersand. I'm tying to send coordinates to get the cursor back to the right spot for user entry.

 

[Rick Rothstein]

Thanks, I see that. With Application.ActiveCell.Column I get an "expression expected compiler error at the Ampersand. I'm tying to send coordinates to get the cursor back to the right spot for user entry.

It is not entirely clear to me what the "right spot" is, but using what you attempted to do as a guide, let me take a guess...

Cells(NewRowNum, ActiveCell.Column).Select