1004 on assignment statement

M

michaeltsmith93

Board Regular
Joined
Sep 29, 2016
Messages
83
Hi there,

Any thoughts on why I'm getting a 1004 error on the statement in red? Presumably the same thing is wrong with the three below it.

Code: 
Sub UpdateTier1ResultsM()


Dim k1 As Worksheet
Dim StartRow As Long, LRow As Long, WMSPCol As Long, MMSEPCol As Long, CDRPCol As Long, WMSScoreCol As Long, MMSEScoreCol As Long, _
    CDRMScoreCol As Long, CDRGScoreCol, AgeCol As Long, WMSScore As Long, MMSEScore As Long, CDRMScore As Long, CDRGScore As Long
Dim FullRange As Range


Set k1 = Worksheets("CognitiveDetails")


StartRow = 6
LRow = k1.Cells.Find("*", searchorder:=xlByRows, searchdirection:=xlPrevious).Row
Set FullRange = Range(Rows(StartRow).EntireRow, Rows(LRow).EntireRow)


WMSScoreCol = k1.Range(Cells(5, 1), Cells(5, 200)).Find("WMS-IV Logical Memory II Total Raw Score - Tier 1", searchorder:=xlByColumns, searchdirection:=xlNext).Column
MMSEScoreCol = k1.Range(Cells(5, 1), Cells(5, 200)).Find("Total eMMSE Score - Tier 1", searchorder:=xlByColumns, searchdirection:=xlNext).Column
CDRMScoreCol = k1.Range(Cells(5, 1), Cells(5, 200)).Find("CDR - Memory Score - Tier 1", searchorder:=xlByColumns, searchdirection:=xlNext).Column
CDRGScoreCol = k1.Range(Cells(5, 1), Cells(5, 200)).Find("CDR - Global Score - Tier 1", searchorder:=xlByColumns, searchdirection:=xlNext).Column


WMSPCol = k1.Range(Cells(5, 1), Cells(5, 200)).Find("Passed WMS? - Tier 1", searchorder:=xlByColumns, searchdirection:=xlNext).Column
MMSEPCol = k1.Range(Cells(5, 1), Cells(5, 200)).Find("Passed MMSE? - Tier 1", searchorder:=xlByColumns, searchdirection:=xlNext).Column
CDRPCol = k1.Range(Cells(5, 1), Cells(5, 200)).Find("Passed CDR? - Tier 1", searchorder:=xlByColumns, searchdirection:=xlNext).Column


AgeCol = k1.Range(Cells(5, 1), Cells(5, 200)).Find("current age", searchorder:=xlByColumns, searchdirection:=xlNext).Column


[COLOR=#ff0000]WMSScore = k1.Cells(r, WMSScoreCol).Value[/COLOR]
MMSEScore = k1.Cells(r, MMSEScoreCol).Value
CDRMScore = k1.Cells(r, CDRMScoreCol).Value
CDRGScore = k1.Cells(r, CDRGScoreCol).Value


For Each r In FullRange


    If IsEmpty(WMSScore) = False Then
    
        If IsEmpty(Cells(r, WMSPCol).Value) = True Then
        
            Select Case AgeScore
                Case AgeCol > 50 And AgeCol < 65 And WMSScore < 16
                Cells(r, WMSPCol).Value = "YES"
                Case AgeCol >= 65 And AgeCol < 70 And WMSScore < 13
                Cells(r, WMSPCol).Value = "YES"
                Case AgeCol >= 70 And AgeCol < 75 And WMSScore < 12
                Cells(r, WMSPCol).Value = "YES"
                Case AgeCol >= 75 And AgeCol < 80 And WMSScore < 10
                Cells(r, WMSPCol).Value = "YES"
                Case AgeCol >= 80 And WMSScore < 8
                Cells(r, WMSPCol).Value = "YES"
                Case Else
                Cells(r, WMSPCol).Value = "NO"
            End Select
        
        End If
        
    End If
    
    
    If IsEmpty(MMSEScore) = False Then
    
        If IsEmpty(Cells(r, MMSEPCol).Value) = True Then
            
            If MMSEScore >= 22 Then
                Cells(r, MMSEPCol).Value = "YES"
                Else
                Cells(r, MMSEPCol).Value = "NO"
            End If
                  
        End If
        
    End If
    
    
    If IsEmpty(CDRMScore) = False And IsEmpty(CDRGScore) = False Then
    
        If IsEmpty(Cells(r, CDRPCol).Value) = True Then
            
            If CDRMScore > 0 And (CDRGScore = 0.5 Or CDRGScore = 1) Then
                Cells(r, CDRPCol).Value = "YES"
                Else
                Cells(r, CDRPCol).Value = "NO"
            End If
                  
        End If
        
    End If
    


Next r






End Sub
 

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.
Sort by date Sort by votes
Maybe I'm missing it but where have you assigned a value to r in the code?
 
Upvote 0
You also best check the result you are getting for FullRange (what is the actual range you get out of this -- @MARK858 this is where r is coming from, for each r in FullRange)
Code: 
Set FullRange = Range(Rows(StartRow).EntireRow, Rows(LRow).EntireRow)

And since you aren't fully qualifying all range references you need to be aware that the code could behave differently depending on which sheet is active (probably should be running with the sheet active that you want to work on). If it's not code in a sheet module, then it's best to always fully qualify range references.

Generally you want to check the runtime values of all variables for debugging problems. See here for tips:
http://krgreenlee.blogspot.com/2006/04/programming-excel-vba-debugging-for.html
 
Last edited:
Upvote 0
@xenou, the line in red is above the for each r in FullRange line and so there is no value assigned to it which it needs within Cells for the line in red.

It then is being used as a range in the for each r in FullRange line and then needs to go back to being a value again in the Cells syntax a few lines down.
 
Last edited:
Upvote 0
michaeltsmith93 said:
Hi there,

Any thoughts on why I'm getting a 1004 error on the statement in red? Presumably the same thing is wrong with the three below it.

Code: 
Sub UpdateTier1ResultsM()
    Dim k1 As Worksheet
    Dim StartRow As Long, LRow As Long, WMSPCol As Long, MMSEPCol As Long, CDRPCol As Long, WMSScoreCol As Long, MMSEScoreCol As Long, _
        CDRMScoreCol As Long, CDRGScoreCol, AgeCol As Long, WMSScore As Long, MMSEScore As Long, CDRMScore As Long, CDRGScore As Long
    Dim FullRange As Range
[COLOR=#0000ff]    Dim r as Range

[/COLOR]
Set k1 = Worksheets("CognitiveDetails")




StartRow = 6
LRow = k1.Cells.Find("*", searchorder:=xlByRows, searchdirection:=xlPrevious).Row
Set FullRange = Range(Rows(StartRow).EntireRow, Rows(LRow).EntireRow)


WMSScoreCol = k1.Range(Cells(5, 1), Cells(5, 200)).Find("WMS-IV Logical Memory II Total Raw Score - Tier 1", searchorder:=xlByColumns, searchdirection:=xlNext).Column
MMSEScoreCol = k1.Range(Cells(5, 1), Cells(5, 200)).Find("Total eMMSE Score - Tier 1", searchorder:=xlByColumns, searchdirection:=xlNext).Column
CDRMScoreCol = k1.Range(Cells(5, 1), Cells(5, 200)).Find("CDR - Memory Score - Tier 1", searchorder:=xlByColumns, searchdirection:=xlNext).Column
CDRGScoreCol = k1.Range(Cells(5, 1), Cells(5, 200)).Find("CDR - Global Score - Tier 1", searchorder:=xlByColumns, searchdirection:=xlNext).Column




WMSPCol = k1.Range(Cells(5, 1), Cells(5, 200)).Find("Passed WMS? - Tier 1", searchorder:=xlByColumns, searchdirection:=xlNext).Column
MMSEPCol = k1.Range(Cells(5, 1), Cells(5, 200)).Find("Passed MMSE? - Tier 1", searchorder:=xlByColumns, searchdirection:=xlNext).Column
CDRPCol = k1.Range(Cells(5, 1), Cells(5, 200)).Find("Passed CDR? - Tier 1", searchorder:=xlByColumns, searchdirection:=xlNext).Column


AgeCol = k1.Range(Cells(5, 1), Cells(5, 200)).Find("current age", searchorder:=xlByColumns, searchdirection:=xlNext).Column


For Each r In FullRange


[COLOR=#0000ff]    WMSScore = k1.Cells([/COLOR][COLOR=#ff0000]r.Row[/COLOR][COLOR=#0000ff], WMSScoreCol).Value[/COLOR]
[COLOR=#0000ff]    MMSEScore = k1.Cells([/COLOR][COLOR=#ff0000]r.Row[/COLOR][COLOR=#0000ff], MMSEScoreCol).Value[/COLOR]
[COLOR=#0000ff]    CDRMScore = k1.Cells([/COLOR][COLOR=#ff0000]r.Row[/COLOR][COLOR=#0000ff], CDRMScoreCol).Value[/COLOR]
[COLOR=#0000ff]    CDRGScore = k1.Cells([/COLOR][COLOR=#ff0000]r.Row[/COLOR][COLOR=#0000ff], CDRGScoreCol).Value[/COLOR]


    If IsEmpty(WMSScore) = False Then
    
        If IsEmpty(Cells(r, WMSPCol).Value) = True Then
        
            Select Case AgeScore
                Case AgeCol > 50 And AgeCol < 65 And WMSScore < 16
                Cells(r, WMSPCol).Value = "YES"
                Case AgeCol >= 65 And AgeCol < 70 And WMSScore < 13
                Cells(r, WMSPCol).Value = "YES"
                Case AgeCol >= 70 And AgeCol < 75 And WMSScore < 12
                Cells(r, WMSPCol).Value = "YES"
                Case AgeCol >= 75 And AgeCol < 80 And WMSScore < 10
                Cells(r, WMSPCol).Value = "YES"
                Case AgeCol >= 80 And WMSScore < 8
                Cells(r, WMSPCol).Value = "YES"
                Case Else
                Cells(r, WMSPCol).Value = "NO"
            End Select
        
        End If
        
    End If
    
    
    If IsEmpty(MMSEScore) = False Then
    
        If IsEmpty(Cells(r, MMSEPCol).Value) = True Then
            
            If MMSEScore >= 22 Then
                Cells(r, MMSEPCol).Value = "YES"
                Else
                Cells(r, MMSEPCol).Value = "NO"
            End If
                  
        End If
        
    End If
    
    
    If IsEmpty(CDRMScore) = False And IsEmpty(CDRGScore) = False Then
    
        If IsEmpty(Cells(r, CDRPCol).Value) = True Then
            
            If CDRMScore > 0 And (CDRGScore = 0.5 Or CDRGScore = 1) Then
                Cells(r, CDRPCol).Value = "YES"
                Else
                Cells(r, CDRPCol).Value = "NO"
            End If
                  
        End If
        
    End If


Next r
End Sub
Click to expand...

Declare r as range.
The code goes inside the For.
Also must be r.Row
 
Upvote 0
@DanteAmor, you also need to change the other occurrences of r within the Cells further down the code to r.Row ;)
 
Upvote 0
Many thanks, all. I will take a look at this later today or tomorrow and get back to you on whether or not I'm successful.
 
Upvote 0
You must log in or register to reply here.

Similar threads

mehidy1437
  • Question
Requesting for experts comments suggestion on my vba code
Replies
5
Views
687
mehidy1437
mehidy1437
Guna13
  • Question
Help to Optimize Macro Code for Faster Performance
Replies
0
Views
740
Guna13
Guna13
E
  • Question
FIFO Macro giving different results each time it runs
Replies
7
Views
801
Lee Wei Sin
L
Y
  • Solved
Error with a mix of .range.cells and with block
Replies
7
Views
314
Akuini
Akuini
S
  • Question
VBA - Finding a repeating number through a long data series
Replies
3
Views
519
StephenCrump
S

Forum statistics

Threads
1,214,520
Messages
6,120,013
Members
448,935
Latest member
ijat

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top