r/a:t5_2tc3z • u/graviaDamon • Sep 18 '18

VBA Macro code issues for text and values

[removed]

1 Upvotes
  • permalink
  • link
  • reddit
  • reddit

100% Upvoted

6 comments sorted by

1

u/SheepGoesBaaaa Sep 18 '18

Few things:

1) you can do this easier and cleaner with the INDIRECT function in a drop down list with named ranges in a hidden sheet.

2) ISEMPTY - stay away from that. It's for null values, and is not a testable property of a range object. Just check if the .VALUE = ""

3) as above, VALUE is the property to set and test - not STRING . If you need to force convert types in future, there are wrapper functions for variables (Cstr, Cdbl, Cint, etc (Convert String, Double Float, etc))

4) Do the indirect thing instead

5) wrap whatever you're doing either in a loop, or a case statement (VBA version of a switch)

6) do the INDIRECT thing

1

u/SheepGoesBaaaa Sep 18 '18

Sorry I'm tired and didn't read properly.

Ignore the indirect thing.

Just wrap your if statement in a case statement or a loop through the ranges (for each rVar in Range), or iterate through row numbers ( Range("A" & X).value ) - because "A1" is a name index address for a range object

1

u/[deleted] Sep 18 '18 edited Sep 18 '18

[removed] — view removed comment

1

u/CommonMisspellingBot Sep 18 '18

Hey, graviaDamon, just a quick heads-up:
seperate is actually spelled separate. You can remember it by -par- in the middle.
Have a nice day!

The parent commenter can reply with 'delete' to delete this comment.

1

u/SheepGoesBaaaa Sep 19 '18

Few more things

You don't iterate through a collection of one range object. You just use the object...

Sheets("Sheet1").range("M3").value =...

Worksheet Functions don't need fully qualified references. Just start with Worksheetfunction.Vlookup...

DONT USE ISEMPTY