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(D172000,""" & 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(D172000,""" & 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(D172000,""" & Range("AI" & i).Text & """)"
End If
End Sub
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(D172000,""" & 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(D172000,""" & 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(D172000,""" & Range("AI" & i).Text & """)"
End If
End Sub