PDA

View Full Version : Range in macro



jaystang
09-20-2018, 04:28 PM
Hello, I've inherited an Excel macro at work that I need to update. I am somewhat familiar with VBA but far from being an expert. Here is a snippet of the code I would like to ask about:

If Len(bad_data_temp) = 0 Then
bad_data_temp = arr1(i, 1) & " in row " & i & "[at sign][at sign][at sign][at sign][at sign]Range(" & column_number & i
End If

I don't understand what the Range and column_number is doing. What is the purpose of the "[at sign]" and why are there 5 of them? What is the "(" at the end of the Range doing? I tried Google but I couldn't find any information about this. Thanks for any help you can provide.

The code actually contains the "at sign" but the forum won't let me post it.

Leith Ross
09-20-2018, 04:58 PM
Hello Jaystang,

It is difficult to answer your question because your code is incomplete. It appears this is part of a loop. Can you post the complete Sub or Function code this came from?

jaystang
09-20-2018, 05:36 PM
Hi Leith,

Here is more code. This macro checks sure that users enter data correctly. For example, this part checks for gender.

'validate Gender
For i = 1 To LastLine Step 1
If i > 2 Then
If Len(arr1(i, 1)) > 0 Then
If arr1(i, 1) <> "MALE" And _
arr1(i, 1) <> "FEMALE" Then
bad_data_in_field = bad_data_in_field + 1
If Len(bad_data_temp) = 0 Then
bad_data_temp = arr1(i, 1) & " - in row # " & i & "[at sign][at sign][at sign][at sign][at sign]Range(" & column_num & i
End If
End If
End If
End If


The macro generates a small report letting the user know what's wrong so they can fix it. For example, if I entered "ZZZZZ" for gender, it would report. This is the exact error message in two columns.
gender ZZZZZ - in row # 3

Thanks for helping.

Leith Ross
09-20-2018, 05:52 PM
Hello Jaystang,

This line is incomplete...



bad_data_temp = arr1(i, 1) & " - in row # " & i & "[at sign][at sign][at sign][at sign][at sign]Range(" & column_num & i


It should look something like this...


bad_data_temp = arr1(i, 1) & " - in row # " & i & "[at sign][at sign][at sign][at sign][at sign]Range(" & column_num & i & ")"


Based on my correction of the code, the bad_data_temp string would look like this...

gender ZZZZZ - in row # 3 @@@@@Range(B3)