concatenate variables

[Linda21]

Can someone review the code below? I simply want to concatenate all 3 variables into one string--no spaces. There is something wrong with the last line of code. Also, should these be Variants if they are all text? Thank you!

SDDF_StartRow = 10
SDDF_FundCol = 2
SDDF_EntityCol = 3
SDDF_CodeCol = 7
D_StartRow = 9
D_FundCol = 33
D_EntityCol = 34 'will change depending on detail sheet
D_CodeCol = 32
D_EntityRow = 5
SDDF_Fund = SDDF.Cells(SDDF_StartRow, SDDF_FundCol) 'designate SDDF fund location
D_Fund = RD.Cells(D_StartRow, D_FundCol) 'designate detail sheet fund location
SDDF_Entity = SDDF.Cells(SDDF_StartRow, SDDF_EntityCol) 'designate SDDF entity location
D_Entity = RD.Cells(D_EntityRow, D_EntityCol) 'designate detail sheet entity location - does not change
SDDF_Code = SDDF.Cells(SDDF_StartRow, SDDF_CodeCol) 'designate SDDF detail code location
D_Code = RD.Cells(D_StartRow, D_CodeCol) 'designate detail sheet detail code location
SDDF = SDDF_Fund & SDDF_Entity & SDDF_Code

 

[kpark91]

SDDF = WorksheetFunction.Substitute (SDDF_Fund + SDDF_Entity + SDDF_Code, " ", "_")

 

[Linda21]

That code does not work either. Error 438: Object does not support this property or method. Any other ideas? Thanks.

 

[kpark91]

Are the variables string? Because it only works when they are string.

 

[Linda21]

They were Variants. I changed to String and now the code below will not work beginning at the '*'. It worked when they were Variants. Thank you.

Dim SDDF_StartRow As String
Dim SDDF_FundCol As String
Dim SDDF_EntityCol As String
Dim SDDF_CodeCol As String
Dim D_StartRow As String
Dim D_FundCol As String
Dim D_EntityCol As String
Dim D_CodeCol As String
Dim D_EntityRow As String

Sub RIGDetailUpdate()

SDDF_StartRow = 10
SDDF_FundCol = 2
SDDF_EntityCol = 3
SDDF_CodeCol = 7
D_StartRow = 9
D_FundCol = 33
D_EntityCol = 34 'will change depending on detail sheet
D_CodeCol = 32
D_EntityRow = 5

'*' SDDF_Fund = SDDF.Cells(SDDF_StartRow, SDDF_FundCol) 'designate SDDF fund location
D_Fund = RD.Cells(D_StartRow, D_FundCol) 'designate detail sheet fund location
SDDF_Entity = SDDF.Cells(SDDF_StartRow, SDDF_EntityCol) 'designate SDDF entity location
D_Entity = RD.Cells(D_EntityRow, D_EntityCol) 'designate detail sheet entity location - does not change
SDDF_Code = SDDF.Cells(SDDF_StartRow, SDDF_CodeCol) 'designate SDDF detail code location
D_Code = RD.Cells(D_StartRow, D_CodeCol) 'designate detail sheet detail code location
SDDF = WorksheetFunction.Substitute(SDDF_Fund + SDDF_Entity + SDDF_Code, " ", "_")

 

[jmthompson]

have you tried changing this

SDDF_Fund = SDDF.Cells(SDDF_StartRow, SDDF_FundCol)

to this (for each variable in question?)

SDDF_Fund = SDDF.Cells(SDDF_StartRow, SDDF_FundCol).Value

 

[Linda21]

I just changed them all. Unfortunately still have an error.

 

[jmthompson]

Which error on which line?

 

[Linda21]

In the second set of code - there is a line with '*'. I changed this to add .Value as you suggested. Error 1004 - application-defined or object-defined error. It all started because of trying to concatenate using the code suggested by other reader. I needed to change all variants to string. The '*' line worked when they were designated as 'variant'. thanks.

 

[jmthompson]

Try changing the starteow variables to long, the col variables to range. What is sddf set as?