Excel VBA Topic 2.5 - Absolute vs Relative References in VBA

[jasonjewik]

I love these videos. Thanks a lot. It's sad that less and less people are leaving comments as the course goes on. 

[shahzadasohailmustafa]

very good and helpful. i am taking all classes

[amandawilson]

Great video. This video helped me fix a problem with a spreadsheet I created where I couldn't figure out how to get the user back to column 1 but 1 row down after entering a variable in column 15. Thanks!

[ToddCoburn]

Dr Nissenson has done a fine job with these.  You may want to check the discussion boards on Openeducation's Bb site where the course is managed, as folks may be using that forum for getting help since it is where Dr Nissenson and his assistants are focusing their response efforts.

[rahmarabea]

Very helpful indeed thank you !!
And i mean all the topics btw

[VioletaAyon]

Really helpful videos, thank you so much!

[haroldmcbroom]

I have a problem I was wondering if you have ever run across?

I'm trying to turn a MS Excel "sheet" into a "sandbox" universe, where each cell can contain one of the following, (nothing, shipname, planet, station, resource node). Everything was going fine from columns "A..Z", until I ran into the borders of "AA..AZ", "AAA...AAZ", etc

I have an integer variable named PlrRadar, which can be improved through "upgrades" obtained while playing the game. Default value is 2, meaning we only have access to information contained within the radar range which is 2 spaces in every direction of the ship.

Here's a fragment of the code:

'Testing the current column of the active sell where our spaceship is
Select Case ActiveCell.Column
'...the code below only executes if the column is between "A..Z"
Case 1 To 26:
'If the result of the current column offset (minus) the value of PlrRadar would result in a run-time error being less than "A", then assign
'TmpRCol1the value of "A".
If (64 + (ActiveCell.Column - PlrRadar)) < 65 Then
TmpRCol1 = Chr(65)
Else
TmpRCol1 = Chr(64 + ActiveCell.Column - PlrRadar)
End If
Range(Chr(64 + ActiveCell.Column) & ActiveCell.Row & ".." & TmpRCol1 & ActiveCell.Row).Select


'HERE'S where the problem is... The Chr() function can no longer be used at this point, because Chr(64+27), that is Upper "A" + 26 would be "upper "Z", add one more making it 27, would not be "AA", but some other keyboard character. I've been using this algorithm since around '2001, 2002, back when there were only 65536 rows in an Excel Spreadsheet, but now there are over a million. I never went past A..Z on any of 'my jobs for which this problem has ever existed for me.

'...the code below only executes if the column is between "AA..AZ"
Case 27 To 52:
Else
End If

[haleyildiz]

Thanks for these videos, they are great. One question though, what is the difference between using Range("A1") = 2 and using Range("A1").Select and ActiveCell.Value = 2 ??

[ravihoratti]

Good Video... But what if the offset function reference a negative column which does not exists, how to handle it?

[annpattara]

i am getting a run-time error '1004'. please help