Guys,
Struggling to get values to be retrieved from a spreadsheet. Someone elses bit of code and am shocking at reading and writing code but you might be able to help.
Procedure which is populating a column (number 7) with "Other" if Column 4 is DCM or and column 7 is not populated or something. Basically my problem is that column 7 is cropping up with a #NAME? error which i thought that meant that it didn't recognise one of the names which are used in the source code.
=VLOOKUP(User Name,Employee.csv!emea,31,FALSE) is the lookup which is producing this error.User Name is a column in spreadsheet (User Stats) and picking up the subsequent value in the s/s Employee.csv range "emea" column 31.I have the s/s open so it should be reading it in but doesn't appear to be running correctly. I haven't included all the code for this piece but there is not a huge amount so if someone has a moment let me know i can send it on to you.
Public Sub OtherCalc()
Dim currentSheet
Set currentSheet = findSheet("User Stats")
Dim index As Integer
findSheet("User Stats").Activate
'd.Add "Conduits", "4"
'd.Add "key", "1"
Dim d, i 'Create some variables
Set d = CreateObject("Scripting.Dictionary")
d.Add "PFG CENTRAL EXP", "1" 'Add some keys and items.
d.Add "SECONDARY LOAN TRADING 4", "1"
d.Add "CONDUITS 4", "1"
d.Add "UK HIGH YIELD CORP FINANCE 4", "1"
d.Add "FS 4", "1"
d.Add "FIGRE 44289", "6"
d.Add "GENERAL INDUSTRIES 4", "1"
d.Add "DCM CO LOAN CAPITAL MARKETS 44", "1"
d.Add "DCM CO LOAN DISTR DESK 4", "1"
d.Add "LONDON CAPITAL MARKETS 44289", "1"
d.Add "RATINGS ADVISORY 44289", "1"
d.Add "EMEA DCM MGMT-H-CHASE 4", "1"
d.Add "JPSPRD EUR-BNK063723516UK 4428", "1"
d.Add "JPRESF 063726578UK 4", "1"
d.Add "JPEM EU CAP MK063703207UK 4428", "1"
d.Add "JPEURP CREDT A063726676UK 4428", "1"
d.Add "CAZ JV DMC", "18"
index = 1
Do While index < 1000
If (currentSheet.Cells(index, 4)) = "DCM" Then
If Not (d.Exists(currentSheet.Cells(index, 7).Value)) Then
currentSheet.Cells(index, 7) = "Other"
End If
End If
index = index + 1
Loop
End Sub
Struggling to get values to be retrieved from a spreadsheet. Someone elses bit of code and am shocking at reading and writing code but you might be able to help.
Procedure which is populating a column (number 7) with "Other" if Column 4 is DCM or and column 7 is not populated or something. Basically my problem is that column 7 is cropping up with a #NAME? error which i thought that meant that it didn't recognise one of the names which are used in the source code.
=VLOOKUP(User Name,Employee.csv!emea,31,FALSE) is the lookup which is producing this error.User Name is a column in spreadsheet (User Stats) and picking up the subsequent value in the s/s Employee.csv range "emea" column 31.I have the s/s open so it should be reading it in but doesn't appear to be running correctly. I haven't included all the code for this piece but there is not a huge amount so if someone has a moment let me know i can send it on to you.
Public Sub OtherCalc()
Dim currentSheet
Set currentSheet = findSheet("User Stats")
Dim index As Integer
findSheet("User Stats").Activate
'd.Add "Conduits", "4"
'd.Add "key", "1"
Dim d, i 'Create some variables
Set d = CreateObject("Scripting.Dictionary")
d.Add "PFG CENTRAL EXP", "1" 'Add some keys and items.
d.Add "SECONDARY LOAN TRADING 4", "1"
d.Add "CONDUITS 4", "1"
d.Add "UK HIGH YIELD CORP FINANCE 4", "1"
d.Add "FS 4", "1"
d.Add "FIGRE 44289", "6"
d.Add "GENERAL INDUSTRIES 4", "1"
d.Add "DCM CO LOAN CAPITAL MARKETS 44", "1"
d.Add "DCM CO LOAN DISTR DESK 4", "1"
d.Add "LONDON CAPITAL MARKETS 44289", "1"
d.Add "RATINGS ADVISORY 44289", "1"
d.Add "EMEA DCM MGMT-H-CHASE 4", "1"
d.Add "JPSPRD EUR-BNK063723516UK 4428", "1"
d.Add "JPRESF 063726578UK 4", "1"
d.Add "JPEM EU CAP MK063703207UK 4428", "1"
d.Add "JPEURP CREDT A063726676UK 4428", "1"
d.Add "CAZ JV DMC", "18"
index = 1
Do While index < 1000
If (currentSheet.Cells(index, 4)) = "DCM" Then
If Not (d.Exists(currentSheet.Cells(index, 7).Value)) Then
currentSheet.Cells(index, 7) = "Other"
End If
End If
index = index + 1
Loop
End Sub
