If statement to place in column

[drop05]

I am trying to get my if statement to work but it is not going how i want it, i want it that if the first name value is FRS then cNum and gNum go in those columns (3 and 4), if it is not then go to column 4 for cNum and 3 for gNum

If shtList.Cells(Index + 4, 11).value = "Yes" Then
firstName = rng(i, 11).value
lastName = rng(i, 10).value
End If
If firstName = "FRS" Then
shtMap.Cells(lastRow, 3).value = cNum
shtMap.Cells(lastRow, 4).value = gNum
Else
shtMap.Cells(lastRow, 4).value = cNum
shtMap.Cells(lastRow, 3).value = gNum
End If
below is the code over all

Sub lookup(Index As Integer, cNum As Integer, EStart As Integer, lookupP As Worksheet)

Dim lastRow, i As Integer
Dim rng As Range
Dim checkBoolCat As Boolean
Dim splitList As Variant
Dim item As Variant

If lookupP.Name = "shtTags_1" Then
Set rng = shtTags_1.ListObjects("reference_1").DataBodyRange
Else
Set rng = shtTags.ListObjects("reference").DataBodyRange
End If

Dim lastName, firstName, As String
Dim gNum, sgNum As Integer
Dim val As Variant

If E_Start = 1 Then
lastRow = 3
Else
lastRow = shtMap.Cells(1000000, 1).End(xlUp).Row + 1
End If

For i = 1 To rng(, 1).Rows.count

firstName = rng(i, 2).value
lastName = rng(i, 1).value
gNum = 0
sgNum = 0

val = shtList.Cells(Index + 4, rng(i, 4).value) _
.value

If shtList.Cells(Index + 4, 11).value = "Yes" Then
firstName = rng(i, 11).value
lastName = rng(i, 10).value
End If

If firstName = "FRS" Then
shtMap.Cells(lastRow, 3).value = cNum
shtMap.Cells(lastRow, 4).value = gNum
Else
shtMap.Cells(lastRow, 4).value = cNum
shtMap.Cells(lastRow, 3).value = gNum
End If


lastRow = shtMap.Cells(1000000, 1).End(xlUp).Row + 1

Next i

End Sub

 

[Joe4]

OK, a few things.

Firstly, when posting VBA code, it is very helpful if you use Code tags. It maintains all spacing, differentiates your code from your explanation, and makes your code much easier for us to read:

How to Post Your VBA Code

Post your VBA code.

www.mrexcel.com

Also, it is often helpful if you post a sampling of your data and expected output, so we can see exactly what the data structure looks like, what the data you are working with looks like, and what exactly your expected result is.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

Now, on to your code, before we even try to analyze, there are a few things that stick out like a sore thumb. You have violated one of the most important rules of VBA - NEVER use reserved words (words of existing function, methods, properties, and objects) as the names of your sub procedures, user defined functions, or variables. You have used "Lookup" as the name of one of your sub procedures, and "Index" as the name of one of your variables. Both of these are names of existing Excel functions.

Using reserved words like that can cause all sorts of issues and unexpected behavior, as when you reference it, Excel cannot be certain whether you are referring to the native functions, or your procefures and variables. I wouldn't even try to analyze your code until those issues have been corrected and you have tried it again (we want to eliminate those potential error factors first).