Home |
Search |
Today's Posts |
#1
![]()
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
![]()
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 |
#2
![]()
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
![]()
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 |
#3
![]()
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
![]() 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
![]() |
||||
Thread | Forum | |||
2 Questions | New Users to Excel | |||
code not unique find latest date | Excel Discussion (Misc queries) | |||
VLOOKUP for Zip Code Ranges | Excel Worksheet Functions | |||
Conform a total to a list of results? | Excel Discussion (Misc queries) | |||
Macro for changing text to Proper Case | Excel Worksheet Functions |