button different from Module?

Question

    Private Sub CommandButton1_Click()
Dim nbp As Long
Dim i As Long
Dim p As Long
  Dim FV As Variant
  Dim CS As Variant
  Dim K As Variant
  Dim iFV As Integer
  Dim iCS As Double

If Range("B9") = "Semi-Annual" Then
  
  p = DateDiff("yyyy", Cells(4, 3), Cells(5, 3))
  
nbp = p * 2
   
 For i = 5 To nbp + 4
        Cells(5, 10).Value = Cells(4, 3).Value
        Cells(i + 1, 10).Value = DateAdd("m", 6, Cells(i, 10).Value)
        
         Next i
  
For i = 6 To nbp + 5

    Cells(i, 14).Value = Cells(7, 2).Value * (Cells(8, 2).Value / 2)
    
Next i

 FV = Sheet2.Range("J5:J10").Value
  CS = Sheet3.Range("F1:G8000").Value

  For iFV = 1 To UBound(FV)

    For iCS = 1 To UBound(CS, 2)
  

      If FV(iFV, 1) = CS(iCS, 1) Then
      
        K(iFV, 1) = CS(iCS, 2)
      
      End If

    Next

  Next

   Sheet2.Range("K5:K10").Value = K

End If
 
 End If
 
If Range("B9") = "Annual" Then
 
    nbp = DateDiff("yyyy", Cells(4, 3), Cells(5, 3))
 
        For i = 5 To nbp + 4
        
            Cells(5, 10).Value = Cells(4, 3).Value
            Cells(i + 1, 10).Value = DateAdd("m", 12, Cells(i, 10).Value)

        Next i
    End if 
If Range("B9") = "Quarterly" Then
 
    p = DateDiff("yyyy", Cells(4, 3), Cells(5, 3))
    nbp = p * 4
    
        For i = 5 To nbp + 4
        
            Cells(5, 10).Value = Cells(4, 3).Value
            Cells(i + 1, 10).Value = DateAdd("m", 3, Cells(i, 10).Value)

         Next i
 
End If

 
If Range("B9") = "Monthly" Then ' to choose from a list . 
 
    p = DateDiff("yyyy", Cells(4, 3), Cells(5, 3))
    nbp = p * 12
    
        For i = 5 To nbp + 4
        
            Cells(5, 10).Value = Cells(4, 3).Value
            Cells(i + 1, 10).Value = DateAdd("m", 3, Cells(i, 10).Value)

        Next i
 
End If
 
End Sub

I have added all the code in the button to help. i am not sure if that will help, anyway here is it. if the user chooses semi annual then couple of things take place. Same goes for the rest "ifs" but i need to fix this issue first then move on to the rest. the code to too long, it is simple and not complicated.

You read from `.Value` but write to `.Value2`. There will be differences when it comes to dates stored in cells. — John Alexiou, Dec 27 '20 at 18:23
BTW the code has nothing to do with a button, so the tile is misleading or confusing. — John Alexiou, Dec 27 '20 at 18:25
The code runs normal when there is not button, once i try to use a button to do that it dose not work. It gives nothing. — Ali Mallah, Dec 27 '20 at 18:28
That end if at the end is part of another if statement. If i choose from a list then this happens. — Ali Mallah, Dec 27 '20 at 18:30
you can try it for yourself pick up some dates and try to use a button for that. i have been trying to it for 3 days, and i got nothing. — Ali Mallah, Dec 27 '20 at 18:33
You need to show us what is wrong with the code. Like **how** it produces the wrong results, or what errors it presents. There is nothing inherently wrong with the code above, and so the mistake could be somewhere else. Where is the code behind the button? What does the data look like? What results do you expect? — John Alexiou, Dec 27 '20 at 19:35
Show the code behind the button, because it seems your module subroutine never gets called. — John Alexiou, Dec 28 '20 at 00:15
This is the code behind the button, i copied and pasted it. I tried to test it before i added it to the main code. but in both the main and the test i am getting nothing. — Ali Mallah, Dec 28 '20 at 07:08
Please [edit] the question and add all the relevant code needed to reproduce the problem. — John Alexiou, Dec 28 '20 at 07:09
When i used your code i got for the first set line ' object doesn't support this property or method'. — Ali Mallah, Dec 28 '20 at 07:34
done i added all the code behind the button, but i need to add more code there, for now i need to resolve this issue. But the code works perfectly, i hope so except for the matching part i have "the issue". — Ali Mallah, Dec 28 '20 at 07:45

John Alexiou · Answer 1 · 2020-12-28T15:14:29.810

Now that more of the code is posted, I think I understand what the problem is.

Wherever you reference Cells() VBA assumes it applies to ActiveSheet. And I think you should fully qualify the calls to be Sheet2.Cells() for example or whatever you need.

When you call the code behind a button, the button resides on a sheet and it references the cells on that sheet. But when you moved the code to a module it no longer referenced the sheet with the button, but whatever other sheet was active at the time.

So whenever you see Cells() or Range() without a worksheet specification in front of it, change it so that it you target a specific worksheet.

PS. Avoid using Integer and prefer Long instead. Also, prefer relative referencing such as Sheet2.Range("G2").Cells(i,j) instead of absolute referencing Sheet2.Cells(1+i, 6+j) or string math such as Sheet2.Range("G" & 1+i & ":G" & 5+i).

button different from Module?

1 Answers1