PDA

View Full Version : [SOLVED] Problem with simple loop!



gersemale
05-25-2005, 06:05 AM
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
05-25-2005, 06:27 AM
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
05-25-2005, 06:36 AM
Just use a worksheet formula


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

gersemale
05-25-2005, 06:40 AM
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
05-25-2005, 06:43 AM
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
05-25-2005, 06:52 AM
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
05-25-2005, 07:47 AM
Try this.


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

sandam
05-25-2005, 07:57 AM
Thanks Norie - was about to post that piece of code myself.

As they say in the classics - My bad :blush



Andrew;?

gersemale
05-25-2005, 07:57 AM
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
05-25-2005, 08:06 AM
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
05-25-2005, 08:21 AM
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
05-26-2005, 01:21 AM
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
05-26-2005, 09:40 AM
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
05-27-2005, 12:35 AM
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
05-27-2005, 02:04 AM
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
05-31-2005, 01:25 AM
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
05-31-2005, 01:30 AM
edit, i really should look at the top of the page before i post :banghead ... ignore that last line of my post :)