Continuous running of Excel VBA

Status
Not open for further replies.

Lucky0683

New Member
Joined
Jun 28, 2018
Messages
4
Hi

I am completely new to VBA so please bear with me if my problem is rudimentary.

I am trying to write a function that will loop through each row in a certain column and compare to another sheet's criteria. if it contains "x", for example, then the value will be returned. However, when I try running the code, the codes run forever and causes the computer to hang.

Here's the code that I have written so far. PS: I have obtained errors when using 'Application.WorksheetFunction.Index' and when reading other threads, it was suggested to delete 'WorksheetFunction'. I'm not sure if that causes the problem and I would also like to clarify the rationale behind removing the words 'WorksheetFunction'

I would also like to see if there is a more efficient way of writing the code as I am not sure what is the last filled cell in the range (so I need a more dynamic referencing as compared to Q655536.

Thank you so much in advance!


Function fnc_Input()


Dim ws As Worksheet: Set ws = ActiveSheet


Dim rng As Range
Set rng = ws.Range("Q4:Q655536")


Dim rngCell As Range




On Error Resume Next


For Each rngCell In rng


If rngCell.Offset(0, -13) = "x" Then
rngCell = Application.Index(Sheets("Data").Range _
("D805:D813"), Application.Match(rngCell.Offset(0, -15), Sheets("Data").Range _
("D805:D813"), 1))
ElseIf rngCell.Offset(0, -13) = "y" Then
rngCell = Application.Index(Sheets("Data").Range _
("D27:D34"), Application.Match(rngCell.Offset(0, -15), Sheets("Data").Range _
("D27:D34"), 1))
ElseIf rngCell.Offset(0, -13) = "z" Then
rngCell = Application.Index(Sheets("Data").Range _
("D718:D726"), Application.Match(rngCell.Offset(0, -15), Sheets("Data").Range _
("D718:D726"), 1))
Else: rngCell = vbNullString
End If


Next rngCell


Set rngCell = Nothing
Set rng = Nothing



End Function
 

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.
Status
Not open for further replies.

Forum statistics

Threads
1,224,823
Messages
6,181,181
Members
453,022
Latest member
Mohamed Magdi Tawfiq Emam

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top