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


wrote in message
ups.com...
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

Joerg wrote:
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


wrote in message
ups.com...
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


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.