Writing Excel SS via VB

[gaiya]

Trying to create & manipulate Excel SS via VB. Stuck trying to replicate the Macro Code below as to the syntax required in VB. i.e how to represent a Range of cells.

Any Takers?

Range("A1:A10&quot.Select
With Selection.Interior
.ColorIndex = 19
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
End With

I've got at the moment :

With gbobj_OrderPadxls.Sheets(1).Range(????????)
With .Interior
.ColorIndex = 19
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
End With
.mergecells = True
End With

Also, does anyone know of any references to this subject anywhere? (writing excel with VB)

Cheers.

 

[CajunCenturion]

You should be able to replace your ????? with a string variable. A Rather contrived example would be:

Dim StartCol as String
Dim EndCol as String
Dim StartRow as Integer
Dim EndRow as Integer
Dim MyRange as String

StartCol = "A"
StartRow = 3
EndCol = "C"
EndRow = 10

MyRange = Trim(StartCol) & Trim(StartRow) & ":" & Trim(EndCol) & Trim(EndRow)

With gbobj_OrderPadxls.Sheets(1).Range(MyRange)
...

This would equavlent to .Range("A3:C10&quot



MyRange = Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[gaiya]

Thanks CajunCenturion, Unfortunately your answer falls down in a couple of places. The variables holding the range values need to be numeric values so "A3" is no good. Also, the syntax you have suggested is incorrect. The ":" separating the start & end values is not correct.
Maybe I should explain further.

The first piece of code is taken from a macro within Excel. I wish to perform the 'Range' operation (in VB6) on varying range values to do various operations.

e.g.
In VB, (Not VBA), the following syntax is valid:

For w_x = 4 To 27
With gbobj_OrderPadxls.Sheets(1).Cells(Row, w_x)
.Font.Name = "Arial Narrow"
.Font.Size = 6
.Locked = False
With .Borders
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
End With
Next
[COLOR=/]
where 'Row' & 'w_x' are string variables. If we were to say 'Row=10' then,

Set all cells in row '10', starting at column '4' and ending in column '27' to have Font="Arial Narrow"; Size="6"; be Unlocked; with each cell having a complete border.

This, though long winded, works fine. However, if we were to say that all the cells selected are required to be merged, then no longer can we use the 'For . . Next' loop but have to define a 'Range' of cells instead.

Hence the need for :

With gbobj_OrderPadxls.Sheets(1).{??? Range ???)
.Font.Name = "Arial Narrow"
.Font.Size = 6
.Locked = False
.Merged = True
With .Borders
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
End With
[COLOR=/]

Any takers this time ?

 

[CajunCenturion]

gaiya,
In your original post, you have the following line

With gbobj_OrderPadxls.Sheets(1).Range(????????)

making a reference to the Range Object of the worksheet. The code that I posted will work with the Range Object.

In your reply, you have the following line

With gbobj_OrderPadxls.Sheets(1).Cells(Row, w_x)

making a reference to the Cells property.

The parameters to the Cells are different. If you wish to use the .Range property from your original post, then my initial response will work.

If you wish to use the cells property, then you'll need to convert your row and column references to row and column indexes, and you'll have to use a loop.

The following two code snippets are identical

For Each c in Worksheets("Sheet1&quot.Range("A1:J4&quot
If c.Value < .001 Then
c.Value = 0
End If
Next c

vs

For rwIndex = 1 to 4
For colIndex = 1 to 10
With Worksheets(&quot;Sheet1&quot.Cells(rwIndex, colIndex)
If .Value < .001
Then .Value = 0
End If
End With
Next colIndex
Next rwIndex

Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[CajunCenturion]

I just ran the following code with the expected results

With fExl_WrkSheet
With .Range(&quot;A&quot; & Trim(StartRow) & &quot;:&quot; & &quot;C&quot; & Trim(EndRow)).Interior
.ColorIndex = lInt_ColorIdx
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
End With
End With
Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[gaiya]

Thanks CajunCenturion, One last thing.

Is there any documentation, books etc you could point me to.

Cheers