VBA Object Variable

%3CLINGO-SUB%20id%3D%22lingo-sub-1550835%22%20slang%3D%22en-US%22%3EVBA%20Object%20Variable%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1550835%22%20slang%3D%22en-US%22%3E%3CP%3EI%20keep%20Having%20errors%20with%20the%20below%20code%3C%2FP%3E%3CP%3EPublic%20TheDeck%20As%20Worksheet%3C%2FP%3E%3CP%3EPublic%20NextCard%20as%20Range%3C%2FP%3E%3CP%3EPublic%20Hitlocation%20as%20Range%3C%2FP%3E%3CP%3EPublic%20NumberCardInhand%20as%20Interger%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENumberCardInHand%20%3D%201%3C%2FP%3E%3CP%3ENumberCardinDeck%3C%2FP%3E%3CP%3ESet%20NextCard%20%3D%20TheDeck.Range(%22A%22%20%26amp%3B%20NumberCardInDeck%20%2B%201)%3C%2FP%3E%3CP%3ESet%20HitLocation%20%3D%20TheTable.Range(%22H%22%20%26amp%3B%20(NumberCardInHand%20%2B%2023))%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EDo%20until%20NumberCardinHand%20%3D%202%3C%2FP%3E%3CP%3EHitLocation.value%20%3D%20NextCard.value%3C%2FP%3E%3CP%3ENumberCardinHand%20%3D%20NumberCardInHand%20%2B%201%3C%2FP%3E%3CP%3ENumberCardInDeck%20%3D%20NumberCardInDeck%20%2B%201%3C%2FP%3E%3CP%3ELoop%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThere%20is%20more%20code%20around%20this%20but%20this%20is%20the%20applicable%20part.%20This%20code%20Works%20perfect%20for%20the%20first%20iteration%20of%20the%20loop%20But%20t%20holds%20the%20original%20value%20of%20%22NextCard%22%20%2B%20%22HitLocation%22%20indefinitely%20and%20doesnt%20update%20them%20if%20the%20values%20they%20are%20dependent%20on%20change.%20is%20that%20normal%20and%20are%20there%20any%20workarounds%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1550835%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1551390%22%20slang%3D%22en-US%22%3ERe%3A%20VBA%20Object%20Variable%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1551390%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F742549%22%20target%3D%22_blank%22%3E%40Bigbruce3%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EYou'd%20have%20to%20move%20the%20lines%3C%2FP%3E%0A%3CP%3ESet%20NextCard%20%3D%20TheDeck.Range(%22A%22%20%26amp%3B%20NumberCardInDeck%20%2B%201)%3C%2FP%3E%0A%3CP%3ESet%20HitLocation%20%3D%20TheTable.Range(%22H%22%20%26amp%3B%20(NumberCardInHand%20%2B%2023))%3C%2FP%3E%0A%3CP%3Einside%20the%20loop.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Frequent Visitor

I keep Having errors with the below code

Public TheDeck As Worksheet

Public TheTable As Worksheet

Public NextCard as Range

Public Hitlocation as Range

Public NumberCardInhand as Interger

Public NumberCardInDeck as Interger

 

Set TheDeck = Thisworkbook.Sheets("Deck")

Set TheTable = Thisworkbook.Sheets("Table")

NumberCardInHand = 1

NumberCardinDeck =1

Set NextCard = TheDeck.Range("A" & NumberCardInDeck + 1)

Set HitLocation = TheTable.Range("H" & (NumberCardInHand + 23))

 

Do until NumberCardinHand = 2

HitLocation.value = NextCard.value

NumberCardinHand = NumberCardInHand + 1

NumberCardInDeck = NumberCardInDeck + 1

Loop

 

There is more code around this but this is the applicable part. This code Works perfect for the first iteration of the loop But t holds the original value of "NextCard" + "HitLocation" indefinitely and doesnt update them if the values they are dependent on change. is that normal and are there any workarounds?

1 Reply
Highlighted

@Bigbruce3 

You'd have to move the lines

Set NextCard = TheDeck.Range("A" & NumberCardInDeck + 1)

Set HitLocation = TheTable.Range("H" & (NumberCardInHand + 23))

inside the loop.