VBA code for Names

[breechd]

I am new to Visual Basic. I am, however, an old programmer. I have used RPG, Fortran, Pascal, Cobol...

I have completed one project in VBA and am now tweaking that code for my next project which is very similar. I just need to know how names of persons are reflected in the code. I need to compare the names on a number of sheets to a master spreadsheet and have them upload into the appropriate row for that person.

Any ideas? Thanks! Deb

 

[mtskv5]

Deb,

If you are trying to reference within the same workbook, you could use a lookup function to grab the data you need. *unless I am misunderstanding the issue - which is possible*

 

[xenou]

Using Find, as a method of a range (or of Cells, being all the cells on a worksheet, or the range that is the whole worksheet), is another option. It's not intuitive at first but there is a good example in the Excel help (along with FindNext, which completes the picture if the name might exist more than once).

 

[breechd]

Thank you for the helpful suggestions. I believe the find using range is going to work for this project. I do have the names appearing on multiple sheets, but only in one cell on each sheet. I will be back to ask additional questions if it doesn't work as I anticipate. D

 

[breechd]

Question regarding the same project. I am receiving a run time error '13' Type mismatch. I'm not sure what I'm doing wrong. Here is the code for one sheet...

Option Explicit

Public Function ProcessSheet()

Dim lngRow As Long
Dim lngColumn As Long

For lngRow = 1 To 32
If Sheet1.Cells(lngRow, 1) <> "" And Sheet1.Cells(lngRow, 2) <> "" Then
If Not Module1.AddValue(strItem:=Sheet1.Cells(lngRow, 2), lngScore:=Sheet1.Cells(lngRow, 3)) Then
MsgBox "An error resulted in trying to add this value", vbCritical, "Error"
End If
End If
Next lngRow
End Function

Any ideas would be most welcome. I am ready to throw my computer out the nearest window. Thanks!

 

[xenou]

What's this:

Module1.AddValue

 

[breechd]

It is the Module that this sheet code interacts with. It manages the master sheet where all the other sheets feed into. Do you wish to see the code in the Module that this code is working with? Please let me know.

 

[xenou]

Chances are that whatever is in AddValue is the problem - the rest of the code seems pretty straightforward (there may be a risk of having the wrong type of data in the cells - but that's always the case unless you control data entry or validate it at runtime).

 

[breechd]

xenou, I have been continuing to tweak the code while waiting for your input. You are correct. Here is the code in question. I am trying to get the code to look at a person's name, 22 specific categories and then upload numbers into the cells that relate to that person and that category...any additional thoughts according to the code below would be very welcome. Thanks so much for your useful information so far. D

Public Function AddValue(FullName As String, strItem As String, lngScore As Long) As Boolean

Dim lngRow As Long
Dim lngColumn As Long
Dim lngTargetRow As Long
Dim lngTargetColumn As Long

lngTargetRow = 0
lngTargetColumn = 0
For lngRow = 2 To 6
If Sheet6.Cells(lngRow, 1) = FullName Then
lngTargetRow = lngRow
End If
Next lngRow
For lngRow = 2 To 6
If Sheet6.Cells(lngRow, 2) = strItem Then
lngTargetRow = lngRow
End If
Next lngRow
For lngColumn = 2 To 24
If Sheet6.Cells(2, lngColumn) = lngScore Then
lngTargetColumn = lngColumn
End If
Next lngColumn
If lngTargetRow <> 0 And lngTargetColumn <> 0 Then
Sheet6.Cells(lngTargetRow, lngTargetColumn) = Sheet6.Cells(lngTargetRow, lngTargetColumn) + lngScore
AddValue = True
ElseIf lngTargetRow <> 0 Then
Sheet6.Cells(lngTargetRow, lngColumn) = Sheet6.Cells(lngTargetRow, lngColumn) + lngScore
AddValue = True
ElseIf lngTargetColumn <> 0 Then
Sheet6.Cells(lngRow, lngTargetColumn) = Sheet6.Cells(lngRow, lngTargetColumn) + lngScore
AddValue = True
Else
Sheet6.Cells(lngRow, lngColumn) = Sheet6.Cells(lngRow, lngColumn) + lngScore
AddValue = False
End If
End Function

 

[xenou]

Is it possible the name could be found in (row 2, column 1), but the item found in (row 3, column 2)? That's seems funny at first glance - why wouldn't everything be in the same row? I guess the proof is in the pudding though - if it works, work it. If you run into trouble some sample dummy data may help make it more concrete.