I am new to using VBA code in Excel and I have 2 queries relating to cleaning up the data in a spreadsheet which I am trying to solve.
Is there a way to use or write an Exel function for the user to select a range of numeric and alphanumeric values in one column (eg, 1, 1A, 12, 12B, 39, 39C) and for every cell in the range split off the letter and then automatically place the two separate values (numeric and alpha) in 2 pre-selected cells.
I was thinking of either designing a form in Excel, or using input boxes which would allow a user to select the range for the alphanumeric data to split and also the range for the split data to be placed in for each row in the range selected.
would be selected by the user and then they would select the range of two columns for the data to be split into 2 columns
The next question I have is it possible in Excel VBA to allow the user to select different ranges of data using an inpuit box or a form to pass to the function which can then use the data selected to find related values and then aggregate other cells in the range based on the related values?
if the spreadsheet range is:
and the columns have the following headings and data
Column A[RoundNumber] B[StreetNumber] C[StreetName] D[NoItems]
Line 1 -   [High Street] 
Line 2 -   [High Street] 
Line 3 -   [High Street] 
Line 4 -   [Main Street] 
Line 5 -   [Main Street] 
Line 6 -   [Main Street] 
I want the user to be able to select via a form or an input box any random range in the workbook such as A1..D3 and then select two ranges of related data to be sorted using their related values and also select a range of data to be concatenated based on the data selected and then a range of data to be added together for the related values.
The function would need to identify all values in the range where there are unique values in A1..A6 and each unique value in C1..C6. Therefore for every row in the range where[RoundNumber] =1 and [StreetNumber] = "High Street" then the output to 2 cells further along the spreadsheet would be the concatenation of the value in [StreetNumber] i.e. "23, 25, 27" separated by a comma and the total of [NoItems] i.e. .
Sorry if this is not very clear, as its hard to explain. I have attached an image of the input and desired output.
Many thanks for any assistance in trying to crack these.
Davie, You are asking 3 related question of one task which is realy adding an extra step and more complex requirement of your first question.
Here's how you can parse out the charaters. I've used a function to do the work and another function to check for the correct selection arrangement. You would have to create a user form or create a button for the user to activate this process.
Dim n As Integer
Dim strNew As String, strNew2 As String
Dim strVal As String
Dim vCell As Range
' Make check to ensure correct selection
If Not fAreasCheck() Then
n = 0
For Each vCell In Selection.Areas(1)
strVal = vCell.Value
strNew = Empty
strNew2 = Empty
For i = 1 To Len(vCell)
strChr = Mid(strVal, i, 1)
' add any character you wish to exclude
If Not strChr Like "[A-Z a-z # $ % & *]" Then
strNew = strNew & strChr
strNew2 = strNew2 & strChr
vCell.Value = strNew
Selection.Areas(2).Item(1).Offset(n, 0).Value = strNew2
n = n + 1
'fRemoveChars = strNew
' Function checks for 2 selections in different columns
' Shows Alert message if selection is not correct
Function fAreasCheck() As Boolean
If Not Selection.Areas.Count = 2 Then
msg = "Select 2 Columns for the source and target values"
If Selection.Areas(1).Column = Selection.Areas(2).Column Then
msg = "Selections must be in 2 columns for source and traget values"
fAreasCheck = True
MsgBox msg, vbExclamation
fAreasCheck = False