Hello There,
I have a certain chunk of comma seperated values stored in a VBA string variable. In the active worksheet, the user chooses a cell, where it should be pasted.
Before it could pasted, I want to make sure there is enough room (rows/columns) for this string value and it does not overwrite any existing information.
I would like to have a function which would do the following:
StringValue: (This is a VBA variable data type String with multiple lines (not stored in any cell value)
"Label1,Label2,Label3,Label4,Label5
One,Two,Three,Four,Five
Six,Seven,Eight,Nine,Ten
Eleven,Twelve,Thirteen,Fourteen
Fifteen,Sixteen,Seventeen,Eighten
Nineteen,Twenty,Twenty One, Twenty Two, Twenty Three"
In the above there are 6 Rows & 5 columns
In the Variable String Value:
StringRows = Find the total number of lines (rows).
StringColumns = From the first line, find the number of columns (identification character ','). How many ',' are there + 1
In the Worksheet, from the cursor position:
WorksheetRows = Find the number of empty rows to the down between cursor position and the first non empty cell.
WorksheetColumns = Find the number of empty columns to the right between cursor position and the first non empty cell.
Matching:
If WorksheetRows > StringRows And
WorksheetColumns > StringColumns Then
Return True
Else
Return False
End if
I was able to get the rows of the string value and the worksheet working as below. Wonder if there would be a way to combine the above functionality in one function:
I have attached a sample workbook for your reference.
Thank you. Look forward to hearing from you.
Regards,
Don
I have a certain chunk of comma seperated values stored in a VBA string variable. In the active worksheet, the user chooses a cell, where it should be pasted.
Before it could pasted, I want to make sure there is enough room (rows/columns) for this string value and it does not overwrite any existing information.
I would like to have a function which would do the following:
StringValue: (This is a VBA variable data type String with multiple lines (not stored in any cell value)
"Label1,Label2,Label3,Label4,Label5
One,Two,Three,Four,Five
Six,Seven,Eight,Nine,Ten
Eleven,Twelve,Thirteen,Fourteen
Fifteen,Sixteen,Seventeen,Eighten
Nineteen,Twenty,Twenty One, Twenty Two, Twenty Three"
In the above there are 6 Rows & 5 columns
In the Variable String Value:
StringRows = Find the total number of lines (rows).
StringColumns = From the first line, find the number of columns (identification character ','). How many ',' are there + 1
In the Worksheet, from the cursor position:
WorksheetRows = Find the number of empty rows to the down between cursor position and the first non empty cell.
WorksheetColumns = Find the number of empty columns to the right between cursor position and the first non empty cell.
Matching:
If WorksheetRows > StringRows And
WorksheetColumns > StringColumns Then
Return True
Else
Return False
End if
I was able to get the rows of the string value and the worksheet working as below. Wonder if there would be a way to combine the above functionality in one function:
I have attached a sample workbook for your reference.
Code:
Function CheckRows(pRange As String, pOutput) As Boolean
Dim lTotalRowsOutput
lTotalRowsOutput = Split(pOutput, Chr(10))
lTotalRowsOutput = UBound(lTotalRowsOutput)
Dim lCurrentRowNumber As Long, lFirstNonBlankCell As Long
Range(pRange).Select
lCurrentRowNumber = ActiveCell.Row
Selection.End(xlDown).Select
lFirstNonBlankCell = ActiveCell.Row
If (lFirstNonBlankCell - lCurrentRowNumber) < lTotalRowsOutput Then
CheckRows= False
Else
CheckRows= True
End If
Range(pRange).Select
End Function
Thank you. Look forward to hearing from you.
Regards,
Don
Attachments
Last edited: