Application.Worksheetfunction.Mid - Run-time error '438: Object doesn't support this property or method

[TheWildAfrican]

Hello,

I was wondering if the Application.Worksheetfunction.Mid works within VBA in Excel 2010 . Everytime I execute the macro, it runs a "run-time error 438: Object doesn't support this property or method".

I'm trying to use the "Mid" Excel function to out a piece of text from the middle of a text entry. Please find my code below and advise. Thanks for your help!!

Dim mid_start, mid_end, cnt_columns, i As Integer
Dim pay_status As String

Range("R205").Activate
i = 0

While (ActiveCell.Offset(i, 0).Value = pay_status)
pay_status = ActiveCell.Offset(i, 0).Value


ActiveCell.Offset(i, 21).Value = Application.WorksheetFunction.Mid((ActiveCell.Offset(i, 0).Value), _ ActiveCell.Offset(i - 3, 21).Value, 1)

 

[pgc01]

Hi
Welcome to the board

vba has its own string functions that you can use. They are very similar to the worksheet functions.

Check the vba help for the function Mid().

 

[James Snyder]

I'm not sure that function exists, but you can use the VBA Mid(string, start, how many):

Dim mid_start As Integer
Dim mid_end As Integer
Dim cnt_columns As Integer
Dim i As Integer
Dim pay_status As String

Range("R205").Activate
i = 0

While (ActiveCell.Offset(i, 0).Value = pay_status)
    pay_status = ActiveCell.Offset(i, 0).Value

    ActiveCell.Offset(i, 21).Value = Mid(ActiveCell.Offset(i, 0).Value, _
        ActiveCell.Offset(i - 3, 21).Value, 1) 
Loop While

Here is the actual syntax:
MID(text, start_position, number_of_characters)

 

[TheWildAfrican]

Thanks!!! I'll try that right away!! You guys ROCK!!!!!!!!!!!!

 

[TheWildAfrican]

Hi again,

Thanks for your help, it was quite helpful. However, my code keep freezing on me as I have 80 columns of this line:

ActiveCell.Offset(i, 21).Value = Application.WorksheetFunction.Mid((ActiveCell.Offset(i, 0).Value), ActiveCell.Offset(i - 3, 21).Value, 1)

and ~600,000 rows to loop through. I'd like to know how to reference an absolute cell for the start position (i.e ActiveCell.Offset(i - 3, 21).Value) in VBA.

Thanks for your help.

 

[James Snyder]

Absolute references start with a dollar sign, like $A$33. In VBA, since you are putting a reference in the code, anything that has A1 addressing or Cells(1,1) referencing is as good as absolute. I hope that helps!

 

[TheWildAfrican]

Thanks, man!! Unfortunately, I'm still having issues. I've tried using the absolute reference (hard coded) withiut any luck. If it's okay with you, I'd like to send you a direct message of the codes...Thanks, again.

 

[James Snyder]

Post them here. A private message doesn't contain the history and context of the problem, so I have to jump back and forth. I did notice that you define and set mid_* variables, but never use them. Use "

" tags to post your code. Also, where exactly are you having the problem? Compiling or run-time? Either way, what is being highlighted as the problem?

 

[TheWildAfrican]

Thanks for your response. Excel crashes everytime I run the macro. The first row of the codes works; but excel stop responding afetr the first loop...I don't know if it's because of the enomorous volume of the data....Yes, I initially thought I might need the mid_* variable, I'm not sure there is a need for the variable.... Please find the code below
Sub Doc_()
'RU 2934 uses this code to simplify loan level data
'Columns from "R". Rows from "205" = "R205"
Dim mid_start, mid_end, cnt_columns, i As Integer
Dim pay_status As String
'initialize variables
mid_start = 0
mid_end = 0
cnt_columns = 0 'Cells(202, 39)
Range("R205").Activate
i = 0
'While Not (IsEmpty(ActiveCell.Offset(i, 0).Value))
' pay_status = ActiveCell.Offset(i, 0).Value
' mid_start = i
While (ActiveCell.Offset(i, 0).Value <> "") 'pay_status) '<>""
pay_status = ActiveCell.Offset(i, 0).Value

'getting the loan status

ActiveCell.Offset(i, 21).Value = Mid((ActiveCell.Offset(i, 0).Value), ActiveCell.Offset(i - 3, 21).Value, 1)
ActiveCell.Offset(i, 22).Value = Mid((ActiveCell.Offset(i, 0).Value), ActiveCell.Offset(i - 3, 22).Value, 1)
ActiveCell.Offset(i, 23).Value = Mid((ActiveCell.Offset(i, 0).Value), ActiveCell.Offset(i - 3, 23).Value, 1)

i = 1 + 1
Wend

'i = i + 1
'Wend
End Sub

 

[James Snyder]

You might also be in an endless loop. If "Mid((ActiveCell.Offset(i, 0).Value), ActiveCell.Offset(i - 3, 21).Value, 1)" doesn't evaluate to a start position for the Mid statement, the "While (ActiveCell.Offset(i, 0).Value <> "")" may never happen. Try this line of code and check the immediate window for results:

Debug.Print ActiveCell.Offset(i - 3, 21).Value

Step through the code and see what is being tested. With an enormous amount of data, you won't be able to check much, but it might help pin this down.