Syntax error - can't see why!

[TraceyH]

Hi all. I'm new to VBA so would really appreciate it if someone could have a look at my code and let me know why I'm getting a syntax error - have been staring at it for AGES and can't see where I've gone wrong, so any help would be hugely appreciated!! I've pasted the code below and highlighted in red the syntax issue.


Option Explicit
Dim EventRow As Long, EventCol As Long
Sub Event_SaveUpdate()
With Form
If .Range("E7").Value = Empty Then
MsgBox "Please choose a name for the event (from the list provided) BEFORE saving"
Exit Sub
End If
If .Range("B5").Value = Empty Then 'New Event
.Range("D3").Value = .Range("B6").Value 'Event ID
EventRow = .Range("C99999").End(xlUp).Row + 1 'Find first row without data
Data.Range("C" & EventRow).Value = .Range("B6").Value 'Event ID
Else 'Existing Event
EventRow = .Range("B5").Value 'Event Row
End If
For EventCol = 3 To 27
Data.Cells(EventRow, EventCol).Value = .Range(Data.Cells(1, EventCol.value).Value 'Add event to Data sheet
Next EventCol
.Range("B3").Value = True 'Event Update to TRUE
.Range("E5").Value = .Range("E7").Value 'Event Name in Drop Down List
.Range("B3").Value = False 'Event Update to FALSE
End With
End Sub

[SamT]

EventCol.value

[TraceyH]

Thanks, Sam - why doesn't that work and what should it be?!

[Paul_Hossler]

Should be just



Data.Cells(EventRow, EventCol).Value = .Range(Data.Cells(1, EventCol).Value 'Add event to Data sheet


.Cells takes two Longs, and since EventCol is a Long, it doesn't have .Value property

The left side of the equals sign is correct

[TraceyH]

Thank you for getting back to me so promptly but I'm afraid that that didn't work, either! Have tried renaming the worksheet from Data to DataSheets (as wondered if this could be causing a problem) but it made no difference. Now it won't let me change the name back as it tells me "invalid property value" - this is going from bad to worse!28508

[TraceyH]

OK, went back to an earlier version and started again - worked out that I needed an extra value = Data.Cells(EventRow, EventCol).Value = .Range(Data.Cells(1, EventCol).Value).Value and it seemed to work, but now I'm getting a "Compile Error: Invalid Next control variable reference"?!28510

[Paul_Hossler]

1. A WS has a .Name and a Code Name. Trying to read Properties in your teeny, tiny screen shot in #5, it looks like the code module is 'DataSheets' and the WS name is 'Data'. 'Data' is what shows up on the tab at the bottom of the grid screen

You can use either




DataSheets.Range("A1").Value = 1234

or

Worksheets("Data").Range("A1").Value = 1234




2. Again, trying to read the screen shot in #6, it looks like it's For EventROW, but Next EventCOL



For EventRow = 4 To 27
Data.Cells(EventRow, EventCol).Value = .Range(Data.Cells(1, EventCol).Value 'Add event to Data sheet
Next EventRow


You still have the .Value on the EventCol

[TraceyH]

Hi Paul. Thanbk you for your patience and explanations. I tried changing the WS name to DataSheets to see if that made a difference to the error, but it didn't so I changed it back to Data, hence why each screenshot appears to have a different name. Sorry about the size of the screenshots - if I could upload the SS I would, but I can't see how to attach a file?!

I got the code to work by changing it to:

28512

[TraceyH]

The code
stores the cell address in a range, which is why I need to use two .value - one for getting the real address and another one is for getting value of that real address

[Paul_Hossler]

The code
stores the cell address in a range, which is why I need to use two .value - one for getting the real address and another one is for getting value of that real address


Got it

[TraceyH]

How do I close a thread?

[Paul_Hossler]

Easy

See 3. in my signature

[TraceyH]

Oops, sorry. Done!