I've done my best to get you pointed in the right direction, but your code is difficult to follow as is. Here is what I have:
Sub Test()
Dim wsheet1 As Worksheet
Set wsheet1 = ActiveWorkbook.Worksheets("Sheet1")
Dim wsheet2 As Worksheet
Set wsheet2 = ActiveWorkbook.Worksheets("Sheet2")
' Use the Range object, and not the 'Object' object
Dim data1Range As Range
Set data1Range = wsheet2.Range(wsheet2.Range("C1"), wsheet2.Cells(Rows.Count, 1).End(xlUp))
' I prefer clearer variable names
Dim InputData As Variant
' I am making the somewhat dangerous assumption here that the only data on this sheet is the data you need in the array.
InputData = wsheet2.UsedRange.value
' I am creating this boolean because I am a little too lazy to do a fancier function approach.
' This is purely to get a satisfied condition from the Select Case statement, and as a result to skip the corresponding conditional block.
Dim SatisfiesConditions As Boolean
' I dont know what kind of value you're retrieving from the corresponding cells, so I am using variants as a temporary type.
Dim MatchedValue As Variant
Dim OffsetValue As Variant
Dim SearchNum As Range
Dim FirstAddress As String
Dim x As Long
' I assume here that your data has headers, so I loop from the lowest bound (first row) plus 1 (offset for header row) to the maximum row
For i = LBound(InputData, 1) + 1 To UBound(data1, 1)
' To use an array, reference the array index and not the worksheet index
' Additionally, use 'Select Case' for multiple 'Or' conditions.
SatisfiesCondition = True
Select Case InputData(i, 3)
Case "Mike"
Case "Sam"
Case "George"
Case "Abrahim"
Case Else
SatisfiesCondition = False
End Select
If SatisfiesCondition Then
MatchedValue = InputData(i, 3)
' Since the offset was 7, 3 + 7 = 10
OffsetValue = InputData(i, 10)
' From here down, it is difficult to understand what you are doing. xOffset isn't declared (or assigned)
If InStr(1, xoffset, "1.") <> 0 Then
With wsheet2.Range("a1:a" & lastrow)
Set SearchNum = .Find(wsheet2.Range("J" & i).value, LookIn:=xlValues)
If Not SearchNum Is Nothing Then
FirstAddress = SearchNum.Address
' This loop should be infinite since searchNum will always be assigned to the same exact
' address, and as a result the condition will always be satisfied.
Do
SearchNum.Offset(columnoffset:=3).Copy
wsheet1.Range("B" & x).PasteSpecial
Loop While Not SearchNum Is Nothing And SearchNum.Address <> FirstAddress
x = x + 1
End If
End With
End If
End If
Next
End Sub
I noticed a few points where you code looks as though it shouldnt actually work. You also had a mix of variables declared and never used, and variables used but never declared.
The Big Stuff
First, multiple Dim statements are always preferable to a single Dim statement for multiple variables
Dim Foo as Long
Dim Bar as String
Dim Baz as Worksheet
Is better than
Dim Foo as Long, Bar as String, Baz as Worksheet
It is also best to declare variables as close to first use as possible (outside of loops though). You'll notice that I implemented this in my refactoring of your code.
Option Explicit
I highly encourage the use of Option Explicit within your code. This will give you a compile-time error any time you try to compile your code when a variable is used but undeclared. This helps with things like:
Dim Fooo as String
Fooo = "SomeString"
...many lines later...
If Foo = "SomeString" Then
' Never is satisfied since Foo is not the same as Fooo
End If
To make Option Explicit easy, do this:
Alt + T
Alt + O
Editor Tab
Make sure 'Require Variable Declaration' is checked
Indentation
Make sure your code is properly indented before posting it. If you did, you would've noticed that you were missing the Next at the end of your For block (which prevents the code from compiling). You would've also noticed the lack of Sub and End Sub.
Select Case vs. If...Or...Or...Or...Or...
Any time you are doing multiple "If it is this, or that, or..." conditionals, you should use a Select Case statement. There is a slight performance increase, but it is also cleaner and easier to read.
Additionally, it makes it easy to edit the conditions as needed.
Select Case works by taking an input variable, and checking the condition against Case's. If the condition matches, it runs the code inside the Case block, otherwise it goes to Case Else or End Select in the absence of Case Else.
For example:
Dim Foo as String
Foo = "Apple"
Select Case Foo
Case "Apple"
msgbox "Foo is an Apple!"
Case "Pear"
msgbox 'Foo is a Pear!'
Case "Orange"
msgbox "Foo is an Orange!"
Case Else
msgbox "Foo is an " & Foo & "!"
End Select
' Outputs a messagebox that says "Foo is an Apple!"
Dim Foo as String
Foo = "Banana"
Select Case Foo
Case "Apple"
msgbox "Foo is an Apple!"
Case "Pear"
msgbox 'Foo is a Pear!'
Case "Orange"
msgbox "Foo is an Orange!"
Case Else
msgbox "Foo is an " & Foo & "!"
End Select
' Outputs a messagebox that says "Foo is a Banana!"
The beauty of Select Case is that you can have code within the Case Blocks or you can leave them empty and only have code in the Case Else block.
Arrays
This is the meat of what you are looking for. From your code, it looks like you misunderstood how to use arrays. Basically, arrays improve performance when the computations are done in the array (this is because they are being done in-memory versus in-worksheet).
When using an array for your purposes I would suggest setting the array equal to the entire range with your data in it. So for example, if you have a table on worksheet Sheet in `Range("A1:Z100") your array would be:
Dim SomeArray as Variant
SomeArray = ThisWorkbook.Worksheets("Sheet1").Range("A1:Z100").Value
This will return a 1-Based array that you can then loop through. Now, for example, if Column "C" should equal "B" * "A" you can do this:
Dim i as Long
For i = LBound(SomeArray, 1) + 1 to Ubound(SomeArray, 1)
If IsNumeric(SomeArray(i, 1)) and IsNumeric(SomeArray(i, 2)) Then
SomeArray(i, 3) = SomeArray(i, 2) * SomeArray(i, 3)
End If
Next
Then we can quickly output it to the worksheet when we are done:
ThisWorkbook.Sheets("Sheet1").Range("A1:Z100").Value = SomeArray
Voila! We now have completed our computations much faster than if we had done the same thing on the worksheet.
I would recommend practicing with arrays and spending some time understanding what they do, how they work, and how to loop through them. They are very powerful when used well.
Rubberduck
If you haven't heard of it before, there is a tool called "RubberDuck" that can help tremendously with refining your code. It has inspections that can catch a lot of the common mistakes. It also improves the Project Explorer window immensely. Check it out here: http://rubberduckvba.com/
Locals, Immediate, and Watch Windows
If you aren't using these windows already you definitely should be. They make debugging much easier, and it also improves your ability to step through your code. You can find them in the 'View' menu of the IDE.
Best of luck!
