Problem with simple loop!

[gersemale]

Hi folks,

Relatively new to VBA and am trying to write a simple module. I have two worksheets, "tasks" and "worktask_calc".

On the worksheet "tasks" I have a list of user names in column A and a work type in B1 in the form of

WorkTypeA
userA
userB
userC

The worksheet "worktask_calc" is a large sheet containing information in the form of:

userA workTypeA 10.5
userA workTypeC 20
userA workTypeD 12
userB workTypeC 5
userC workTypeA 6
etc..

I wish to return the value in Cell C above to the "tasks" worksheet. ie. for the example above i will return:

WorkTypeA
userA 10.5
userB
userC 6

My code is below, and works fine if the work type and user are present in each iteration on the "workTask_calc" sheet. If they are not, the editor does not respond.

Sub FindTasks()
Dim i As Long
Dim j As Long
Dim name As String
Dim workTypeA As String
Dim workTypeB As String
Dim workTypeC As String
Dim timespent As String
Application.ScreenUpdating = False
' Look for occurances of task B1
Sheets("Tasks").Select
If Range("B1").Value <> "" Then
i = 2
Do Until Range("A" + CStr(i)).Value = ""
Sheets("Tasks").Select
name = Range("A" + CStr(i)).Value
workTypeA = Range("B1").Value
j = 3
Sheets("WorkTask_Calc").Select
Do Until Range("A" + CStr(j)).Value = ""
Sheets("WorkTask_Calc").Select
If Range("A" + CStr(j)).Value = name And Range("B" + CStr(j)).Value = workTypeA Then
timespent = Range("C" + CStr(j)).Value
Sheets("Tasks").Select
Range("B" + CStr(i)).Value = timespent
End If
j = j + 1
Loop
i = i + 1
Loop
End If
Application.ScreenUpdating = True
End Sub

Any help would be much appreciated!

Thanks,

Ger

[gersemale]

Formatting error sorry!

WorkTypeA
userA
userB
userC

should read

<empty> |WorkTypeA
userA
userB
userC

and

WorkTypeA
userA 10.5
userB
userC 6

should read

<empty>|WorkTypeA
userA |10.5
userB |
userC | 6

[Bob Phillips]

Just use a worksheet formula

=IF(ISNA(VLOOKUP(A2,worktask_calc!A2:C1000,3,False)),"",VLOOKUP(A2,worktask_calc!A2:C1000,3,False))

[gersemale]

thanks for your post but I must reference the user name and the worktype.

VLOOKUP will not work in this case as I need to only return the time spent when for instance the user in only A2 and the worktype in B1 are listed in worktask_calc sheet

[sandam]

Hi and welcome to VBAX

Give this a shot

Sub FindTasks()
Dim i As Long
Dim j As Long
Dim LastRowTasks As Integer
Dim LastRowWorkTask As Integer
Dim name As String
Dim workTypeA As String
Dim workTypeB As String
Dim workTypeC As String
Dim timespent As String
Application.ScreenUpdating = False
' Look for occurances of task B1
Sheets("Tasks").Select
If Range("B1").Value <> "" Then
i = 2
LastRowTasks = Range("A65536").End(xlUp)
Do Until i = LastRowTasks
Sheets("Tasks").Select
name = Range("A" + CStr(i)).Value
workTypeA = Range("B1").Value
j = 3
Sheets("WorkTask_Calc").Select
LastRowWorkTask = Range("A65536").End(xlUp)
Do Until j = LastRowWorkTask
Sheets("WorkTask_Calc").Select
If Range("A" + CStr(j)).Value = name And Range("B" + CStr(j)).Value = workTypeA Then
timespent = Range("C" + CStr(j)).Value
Sheets("Tasks").Select
Range("B" + CStr(i)).Value = timespent
End If
j = j + 1
Loop
i = i + 1
Loop
End If
Application.ScreenUpdating = True
End Sub

[gersemale]

Thanks Sandam!

Received a type mismatch error.

LastRowTasks = Range("A65536").End(xlUp)

Do I have to cast to an int? If so how do I do that in VBA??

Thanks again

[Norie]

Try this.

LastRowTasks = Range("A65536").End(xlUp).Row

[sandam]

Thanks Norie - was about to post that piece of code myself.

As they say in the classics - My bad



Andrew;?

[gersemale]

Thanks Norie, it works now however it is not returning the correct values if a user name does not have the worktype associated with it, that is if i am looking for userA and worktypeA and I do not find it the result should be nothing.

[sandam]

This might work, alhtough it might help if you could tell us what value its returning for nothing.

HTH
Andrew;?

If Range("A" + CStr(j)).Value = name And _ 
Range("B" + CStr(j)).Value = workTypeA Then                    
  timespent = Range("C" + CStr(j)).Value 
  Sheets("Tasks").Select                    
  Range("B" + CStr(i)).Value = timespent
Else            
  Sheets("Tasks").Select                    
  Range("B" + CStr(i)).Value = "0" 
 End If

[gersemale]

After adding your code for the following Example:

Name | workA
userA | 0:00:00
userB | 0:00:00
userC | 36:23:00

on the workTask_calc sheet the values were:

userA | workA | 1
userA | workB | 10
userB | workB | 5
userB | workD | 8
userC | workA | 3
userC | workB | 2

[sandam]

Mulled over this on the way home last night and again this morning and from what I could see, the could should have worked.
Then I changed two lines of code and quess what - it worked.

if you take that minor rewrite of your code and change each of the Do Until loops so that it reads

Do Until i = LastRowTasks +1

and the other one to

Do Until j = LastRowWorkTask + 1

it should work.

I've tested your code on Excel 2003. When I made this modification, it worked for me.

HTH
Andrew;?

[gersemale]

Thanks Sandam! Excellent, have managed to get it to work expect for one problem.

I am looking for two criteria in the workTask_Calc sheet the userA and WorkTypeA. To trouble shoot this I have split up the look firstly to only look for occurances of WorkTypeA and it found all. I then modified the module to only look for occurances of the user name and it came up with nothing!! I am pressuming that the format of the cells are incorrect (i have copied the value of userA from the tasks sheet and using find found it in the workTask_Calc sheet so it definately exists on the workTask_Calc sheet!), which may occur through use of this program and thus we are getting no match. Can I cast each cell in A to a string to ensure it will match the type I have set in the module?

Thanks again for helping me with this guys!

Ger

[sandam]

string comparing is always a tricky business. In word tables there is an end of cell marker which is made up of a Chr(13) and a chr(7) and this can affect data pulled from there. With excel I'm not to sure. It may be that there are spaces at the end of the usernames and that can affect it. For example "UserA " = "UserA" will be false. But if you use say Instr and go with If InStr(1,"UserA","UserA ",1) > 0 Then ... It will come back with a value greater than 0 i.e True, but use the text compare method of InStr as it ignores case. Binary Comparison is case dependant (hence the 1 at the end of the function call and not a 0. The 1 at the begining is to tell it to start searching at the 1st position in the strings.

[gersemale]

That was it exactly! Going forward it will be easiest to just append a whitespace to the end of the search variable.

Thanks for all your help Sandam!

[sandam]

Excellent. If you usernames are without spaces per say. Like TheUserA then you can remove all spaces from the string. If thats the case I have something that could help you. Otherwise, if it works, don't fix as they say.

HTH

Andrew

p.s if this is solved then you can mark the thread solved by using the thread tools at the top of the page.

[sandam]

edit, i really should look at the top of the page before i post :banghead ... ignore that last line of my post