why does my If statement change the Activecell to "TRUE"?

[Teladianium]

Hi all,

I can only find help on "IF" statements for formulas in Excel help. And I don't think I m asking the correct question to google. so a am hoping I can find it here. I think it will be a silly thing on my part I'm sure. but my IF changes the active cell to "TRUE" when running. I do not wish it to do so as the active cell is critical data. I tried to use the XL2BB but it keeps crashing for some reason. but it's just a data input screen, nothing special (copy & Pasted it at the bottom just for reference) Here is my code. It's intended to check the "Sample nature" column to see if it's a "BHS Sample" and do nothing/skip if it is. and call the "Createsheet" sub if not. then go to the next line and repeat. Thanks in advance, much appreciated.

Sub SurfaceSampleDataSheet()
Dim BHSTest
BHSTest = ActiveCell.Offset(21, 0).Value
Do Until ActiveCell = "Version 3.1"
BHSTest = ActiveCell.Offset(21, 0).Value
If BHSTest <> "BHS Samples" Then
Call CreateDataSheet
End If
ActiveCell = ActiveCell.Offset(1, 0).Select
Loop

End Sub

No	Date	Time	Sample Nature	Sampler
				Type
DST_2.01	11-Dec-22	12:00	Brine	N/A
TRUE	14-Dec-22	18:20	Water	N/A
TRUE	15-Dec-22	1:00	BHS Samples	N/A
TRUE	15-Dec-22	3:00	Water	N/A

 

[Joe4]

What are you trying to do here?

ActiveCell = ActiveCell.Offset(1, 0).Select

You would NEVER put a "Select" statement in an equality expression - that makes no sense.
You use "Select" to physically select a cell.
If you want to set the ActiveCell equal to the the value in the row below it, you should use:

ActiveCell = ActiveCell.Offset(1, 0)

 

[Teladianium]

Hi Joe,
Thank you,
I was looking to actually select the cell below after the checking. Each cell in the column needs to be tested. I get what you mean by my statement not making sense.

So I removed the .select and then it was copying the cell below, just as you said. I then changed it to just Activecell.offset(1,).select and the cell below was selected and no changes were made to the data.
Success

I knew it would be something daft. I was convinced it was my If statement, i never even considered it would have been the offset that was the problem.
Fantastic. Thank you for your time, and your knowledge, I will take a step back in the future and consider more of the bigger picture. Much appreciated

 

[Joe4]

You are welcome.

Note that there a few things you can do to improve your code.
Loops are notoriously slow. Sometimes they are necessary, but if there are other ways of doing it more efficiently, they should be avoided.
Second, it is not necessary to actually select cells in order to work with them, and selecting cells slows your code down too (so selecting cells within a loop will REALLY slow the code down!).

I am not sure what column you are looping through, but if it was column A, you could do something like this:

Dim lr as Long
Dim r as Long

'Find last row in column A with data
lr = Cells(Rows.Count, "A").End(xlUp).Row

'Loop through all rows
For r = 1 to lr
'   Do your stuff here
'   ...
'   Exit if current cell is "Version 3.1"
    If Cells(r, "A").Value = "Version 3.1" Then Exit For
Next r

 

[Teladianium]

Hi Joe,

I think by what you are saying the code could probably do it without selecting the cell, but for me, I think that might be too advanced just now.

The loop quickly(or slowly as it seems) checks the sample type and then runs the other sub. The other sub takes all the data from each column (qty17) and assigns it to variables to later fill in a blank form. I can see it would be possible maybe with the "offset", so may give it a try once I get this part up and running, I will definitely try changing the current "do" loop for your "for" loop, I have seen many people use this and I have always defaulted to the do loop for safety/comfort. I need to learn how they work so I think now is the time.
Again, thank you so much for your time.