VBA does a lot of things behind your back, to "make things easier". The problem is that these things come back and bite you later.
Undeclared variables, for example. Without Option Explicit specified at the top of every module, VBA will happily compile code that uses variables that aren't declared. Sounds useful? It would be, if the types were inferred. But instead VBA declares an on-the-fly Variant, that holds whatever you put into it (and changes its type to accomodate whatever you're assigning).
a = UsedRange.Rows.Count
Here a is an implicit Variant/Long. Declare it as a Long integer:
Dim a As Long
Even better, give it a meaningful name:
Dim usedRows As Long
usedRows = UsedRange.Rows.Count
Now the fun part:
Testvariable = Range("A1", "A" & a)
Here Testvariable is an implicit Variant/Array. How so? The code that VBA sees looks something like this:
Testvariable = Range("A1", "A" & a).Value
And because a wouldn't be 1, that .Value is referring to more than one single cell - Excel's object model gives you an array that contains the values of all cells in the specified range, and that's what Testvariable contains.
If you wanted Testvariable to refer to a range rather than just their values, you would declare a Range object variable, and assign it with the Set keyword:
Dim testVariable As Range
Set testVariable = ActiveSheet.Range("A1:A" & a)
Notice the explicit ActiveSheet here: without it the code does exactly the same thing, so why put it in? Because you want to be as explicit as possible: unqualified Range, Cells, Rows, Columns and Names calls all implicitly refer to the active worksheet - and that's yet another source of bugs (just google up "range error 1004", you'll find hundreds of Stack Overflow questions about it).
If you meant testVariable to contain the values of every cell in that specified range, then you would declare and assign testVariable like this:
Dim testVariable As Variant
testVariable = ActiveSheet.Range("A1:A" & a).Value
And now you have an array that contains all values in range "A1:A" & a of the active worksheet: that's what your code does.. implicitly.
FirstNo = Left(Testvariable, 1)
So now we want the "first number", and we're reading it off a variant array.
The Left (or Left$) function is from the VBA.Strings module, and means to works with strings; it can work with other types too (with implicit type conversions), but if you give it an object reference or an array, it won't know how to convert it for you, and VBA will raise a run-time error.
The testVariable variant array contains Variant values: most cells will contain a Double floating-point value. Others will contain a String. Some cells can contain an error value (e.g. #N/A, #VALUE!, or #REF!) - and VBA will not be able to implicitly convert such error values either.
So before you read any cell's value, you need to make sure you can read it; use the IsError function for that:
If IsError(testVariable(1, 1)) Then
Exit Sub ' cell contains an error; cannot process
If Not IsNumeric(testVariable(1, 1)) Then
Exit Sub ' cell doesn't contain a number; cannot process
End If
' now that we KNOW our value is a numeric value...
Dim firstNumber As Double
firstNumber = testVariable(1, 1)
Notice that (1, 1)? That's because testVariable is a 1-based 2D array (without Option Base 1 implicitly sized arrays are always 0-based, except when you're getting one from a Range), so to read the value in the first row / first column, you need to read the value at index (1, 1).
But that's not what you're trying to do.
So I was trying to append prefixes to a set of values, depending on the first number of each value.
"Each value" means you need to iterate the values, so you have a loop there.
Dim i As Long
For i = 1 To a
'...
Next
It's not the "first number" we want, it's the firstDigit, of every cell we're looping over.
If FirstNo(i,1) = "1" Then
Here you've lost track of the types you're dealing with: FirstNo was assigned before the loop started, so its value will be constant at every iteration.
You mean to do this:
Dim values As Variant
values = ActiveSheet.Range("A1:A" & usedRows).Value
Dim i As Long
For i = 1 To usedRows
If Not IsError(values(i)) Then
Dim representation As String
representation = CStr(values(i))
Dim prefix As String
If Left$(representation, 1) = "1" Then
prefix = "abc"
Else
prefix = "def"
End If
ActiveSheet.Range("B" & i).Value = prefix & representation
End If
Next
Now that everything is explicit and properly indented, it's much easier to see what's going on... and now I'd seriously question why you need VBA to do that:
[B1] = IFERROR(IF(LEFT(A1, 1) = "1", "abc" & A1, "def" & A1), "")
And drag the formula down.
