Sandra Smith
asked on
VBA not recognizing variable value in excel
I have a process that needs to dynamicallty determine the ending column for a range of data. The strColumnLetter in debug print does return "F". But the code simply does not recognize it.
Public Function DataSourceRatings(strWbkDestination As String, strWbkSource As String) As Boolean 'Actually passing the TEST_Client workbook name here.
'*****************************************************************************
' Copies the RATING worksheet data to the DataSource
' worksheet which is where the Client worksheets will drawn their
' rating data in a query rather than linking to the ActiveMasterWorkoook.
'*****************************************************************************
On Error GoTo ErrorHandler
Dim intLastColNum As Integer
Dim intColNum As Integer
Dim strLastColumn As String
Dim strColumnLetter As String
With Workbooks(strWbkDestination) 'Current month's Green Package destination workbook
.Activate
With .Worksheets("DataSource")
.Activate
.Range("A1").Activate
.Range("A1") = "RATING - AM WORKING ON IT - DO NOT INTERRUPT!"
.Range("B1") = "ClientRatingsDetail"
End With
End With
If Not QueryWorkSheet(strWbkDestination, strWbkSource, "RAT", "A", "ds_RAT") Then
DataSourceRatings = False
Exit Function
End If
'Determine last column number and letter
strLastColumn = Range("IV3").End(xlToLeft).Address
Debug.Print "Last Column: " & strLastColumn
intColNum = Range(strLastColumn).Column
Debug.Print "Col Number: " & intColNum
strColumnLetter = fntColNo2ColRef(intColNum)
Debug.Print "Column Letter: " & strColumnLetter
With Workbooks(strWbkDestination) 'Current month's Green Package destination workbook
.Activate
'Title columns, reformatting and coloring fields
With Worksheets("DataSource")
.Columns("A:" & strColumnLetter).ColumnWidth = 20
.Range("A1:" & strColumnLetter & "3").WrapText = True
.Range("A1:" & strColumnLetter & "3").Font.Bold = True
.Range("A1:" & strColumnLetter & "3").WrapText = True
.Range("A1:" & strColumnLetter & "3").Interior.Color = RGB(198, 226, 255)
.Range("A2").Value = 1
.Range("A2:" & strColumnLetter & "2").DataSeries Rowcol:=xlRows, Type:=xlLinear, _
Date:=xlDay, Step:=1, Trend:=False
End With
End With
'Check to see which type and then delete rows accordingly Call DeleteCertainBlankRows(strWbkDestination, "A", "B", strColumnLetter)
Call CreateDatSourceRangeNames(strWbkDestination, "A", strColumnLetter, "RAT")
'Format column B for proper aligment after all copy/pastes have been completed
With Workbooks(strWbkDestination).Worksheets("DataSource")
.Activate
Columns("B:B").Select
With Selection
.HorizontalAlignment = xlLeft
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
End With
End With
With Workbooks(strWbkDestination) 'Current month's Green Package destination workbook
.Activate
With .Worksheets("DataSource")
.Activate
.Range("A1") = "RATING "
End With
End With
DataSourceRatings = True
Exit_ErrorHandler:
Exit Function
ErrorHandler:
If Err.Number = 9 Then
MsgBox "Error Number: " & Err.Number & vbCrLf & vbCrLf & "Description: " & _
"The file " & strWbkDestination & " does not exist or is not open.", vbOKOnly
DataSourceRatings = False
Resume Exit_ErrorHandler
Else
MsgBox " Error Number: " & Err.Number & vbCrLf & vbCrLf & "Description: " & Err.Description & _
vbCrLf & vbCrLf & " Procedure: DataSourceRatings", vbOKOnly
DataSourceRatings = False
Resume Exit_ErrorHandler
End If
End Function
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Let me try your suggestions. I am simply going around and around.
Sandra
Sandra
ASKER
Worked, thank you. This project has been changed to four month's worth of work in thee weeks so more questions will be coming your way!
Sandra
Sandra
Kevin