PDA

View Full Version : Programming in VBA



Gaius Marius
2011-03-08, 11:57 AM
Good morning everybody (11:55, still the morning!!)

I am currently programming stuff in VBA at my work, and I need some technical support.

I am trying to have a cell that will write down the value of 2 strings I defined earliers. The strings are identified as "MonthValue" and "Year6". What I want is:

MonthValue/Year6

to appear, where MonthValue will be 03 or 06 or 09, etc.. while Year6 will be 09, 10, 11, etc... So it'll give:

03/11

I try to figure out how to have this written down, to no avail. Maybe somebody here has a better Excel-Fu than I?

AlterForm
2011-03-08, 12:07 PM
CONCATENATE(MonthValue_Cell, "/", Year6_Cell) should do it. It works on my copy of Excel 2010.

Gaius Marius
2011-03-08, 12:11 PM
CONCATENATE(MonthValue_Cell, "/", Year6_Cell) should do it. It works on my copy of Excel 2010.

Why adding the _Cell at the end of my StringNames?

SMEE
2011-03-08, 12:13 PM
This is better suited at Friendly Banter.
Thread moved.

Gaius Marius
2011-03-08, 12:28 PM
Okay, I think I found the formula:

Range("B3").Select
ActiveCell = MonthID & "/" & Year6

Thing is. it's typing 12/10, automatically reformating it to 10/12/2011, which isn't what I want. Someone has an idea?

AlterForm
2011-03-08, 12:28 PM
Why adding the _Cell at the end of my StringNames?

Because I've dealt with entirely too many friends who think they've named their variables when they haven't, so I just tell them to use a cell reference anyway. :smallsigh: Which is to say, sorry, habit. Should've seen that you actually put proper identifiers on them.

Just pass your strings into the Concatenate function with a slash string between them.

[EDIT]:
Okay, I think I found the formula:

Range("B3").Select
ActiveCell = MonthID & "/" & Year6

Thing is. it's typing 12/10, automatically reformating it to 10/12/2011, which isn't what I want. Someone has an idea?

:smallconfused: @ Code snippet
:reads topic title:

Oh, I see what's up. Thought you were just doing regular old formulas for some reason. I don't actually have any experience with VBA, although there should still be a concatenate function, or the & operator is defined on strings as such. If you're getting weird output, maybe your cell formatting is off? (Just by guessing at what those calls do, what you have should work from a code-logic standpoint.)

Gaius Marius
2011-03-08, 12:38 PM
:smallconfused: @ Code snippet
:reads topic title:

Oh, I see what's up. Thought you were just doing regular old formulas for some reason. I don't actually have any experience with VBA, although there should still be a concatenate function, or the & operator is defined on strings as such. If you're getting weird output, maybe your cell formatting is off? (Just by guessing at what those calls do, what you have should work from a code-logic standpoint.)

Range("B3:G3").Select
Selection.NumberFormat = "@"

There you go! :-) You have to format it in text!!

Thanks for your help!

Ashtar
2011-03-08, 12:38 PM
The thing with excel is it's trying to interpret the input as dates. There's a couple of ways to stop that, first is to set the cell format to text. Or prefix the text with ' (the single quote) which causes excel to show data "as is" with no reinterpretation.

Gaius Marius
2011-03-08, 12:49 PM
That explains it..

All right, final (and harder) question: I want to play with ranges. Let's say I want the cell located at the coordonate (4,5) of the Range1 get the value of the cell located at the coordonate (23,1345) of the Range 2, what'd be the formula?

Ashtar
2011-03-09, 05:24 AM
If you're doing it in VBA code, and you can work with Named Ranges (in Excel 2007 -> Formulas -> Name Manager, in 2003 it should be in Insert-> Name -> Manage):


Public Sub DoIt()
Dim r1 As Range, r2 As Range

Set r1 = ThisWorkbook.Names("NamedRange1").RefersToRange
Set r2 = ThisWorkbook.Names("NamedRange2").RefersToRange

r2(5, 2) = r1(2, 3)
End Sub

With NamedRange1 and 2 being the names of your ranges. The X,Y coordinates are relative to the inside of the range. so row 5 column 2 of range 2 is now getting the value from row 2 column 3 of range 1.

Hope this helps.

If you're doing it in Excel Formulas, you'll have to go to the cell you want in Range1 and add a formula:

=OFFSET(NamedRange2;5;2;1;1)
The last 2 (Height and Width) are the cell dimensions (in this case 1), the first 2 are the X,Y coordinate of the cell. Warning, here you need to enter the X-1 and Y-1 coordinates desired, since OFFSET(0,0,1,1) is the upper corner of the range.

Gaius Marius
2011-03-16, 09:31 AM
Hello everyone!

I have to thank everybody that helped me previously. So cooperative! :-)

Now, I have another problem, and I hope you might help me...

I am trying to program in a conditional formatting with VBA for a specific range. In short, if any cell in the current selection happens to be FALSE, the cell's font must become white.

Here's what I got:

Range("H20:M32").Select
Selection.FormatConditions.Add Type:=xlTextString, String:="FALSE", _
TextOperator:=xlContains
Selection.FormatConditions(Selection.FormatConditi ons.Count).SetFirstPriority
With Selection.FormatConditions(1).Font
.Underline = xlUnderlineStyleNone
.Strikethrough = False
.ThemeColor = xlThemeColorDark1
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = True


Problem is, my Macro stops for a debug at these lines:

.Underline = xlUnderlineStyleNone
.Strikethrough = False
.ThemeColor = xlThemeColorDark1
.TintAndShade = 0

Telling me this:


Run-time error '1004':

Application-defined or object-defined error

Somebody has an idea why it's bugging me?

Gaius Marius
2011-03-16, 10:58 AM
All right, I managed to rationalise the formula to this:

With Range("H20:M32")
.FormatConditions.Add xlTextString, String:="FALSE", TextOperator:=xlContains
.FormatConditions(1).Font.ColorIndex = 10
End With


IT'S STILL BUGGING! It doesn't want to accept format to the Font. WHY?!

Douglas
2011-03-16, 11:34 AM
Just a guess, but try changing the 1 to a 0. Quite often in programming, array indexes start at 0 instead of 1.