error in this code

  • Thread starter Thread starter forNiloy
  • Start date Start date
F

forNiloy

Hi,
I want to read entered text and do action as follows.But I am getting
error ,in Range().Plz help me to rectify.
getting error at set range line and in the For loop.Code is as follows


Private Sub Copyformula_Click()

Dim str As String
Dim i, x As Integer


strFrom = Mid(UCase(Trim(TxtFrom.Text)), 1, 2)
strTo = Mid(UCase(Trim(TxtTo.Text)), 1, 2)

intFrom = CInt(Mid(Trim(TxtFrom.Text), 3, 2))
intTo = CInt(Mid(Trim(TxtTo.Text), 3, 2))

strModule = UCase(Trim(TxtModule.Text))

Set Range = ActiveSheet.Range(""" & UCase(Trim(TxtFrom.Text) & ":" &
UCase(Trim(TxtTo.Text))& """)

If Not Range.HasFormula Then

'On Error Resume Next

If MsgBox("Those cells contains formula,Do you want to replace ?",
"Check", vbYesNo) = vbYes Then

'On Error Resume Next

For i = intFrom To intTo


ActiveSheet.Range(""" & strFrom & """ & i).Formula =
"=COUNTIF(D17:D2000,""" & ActiveSheet.Range(""" & strModule & """ &
i).Text & """)"


Next i


ActiveSheet.Range(""" & strFrom & """ & intTo + 1).Formula =
"=SUM(" & UCase(Trim(TxtFrom.Text)) & ":" & UCase(Trim(TxtTo.Text)) & "
)"

Else

Exit Sub

End If

'Else

'For x = intFrom To intTo


'ActiveSheet.Range(""" & strFrom & """ & x).Formula =
"=COUNTIF(D17:D2000,""" & ActiveSheet.Range(""" & strModule & """ &
x).Text & """)"


'Next x


'ActiveSheet.Range(""" & strFrom & """ & intTo + 1).Formula =
"=SUM(" & UCase(Trim(TxtFrom.Text)) & ":" & UCase(Trim(TxtTo.Text)) & "
)"
'Range("AJ" & i - 1).Cells.Formula
'"=COUNTIF(D17:D2000,""" & Range("AI" & i).Text & """)"
End If

End Sub
 
People in this NG are exceptionally nice, but if you through code at them
and expect them to find out what it does and why it doesn't work, you are
probably asking too much. Besides: Many of your variables are neither
defined nor explained.

Joerg
 
Joerg said:
People in this NG are exceptionally nice, but if you through code at them
and expect them to find out what it does and why it doesn't work, you are
probably asking too much. Besides: Many of your variables are neither
defined nor explained.

Joerg
Click to expand...

Thanks for your reply.But I just wanted to know the cause of the error
,where I used Range().For reference I had given a part of my code,I
thought it will be more comprehensable.
 
Back
Top