VBA for excel

[yawningdog]

I need to confer with a VERY clever excel programmer. Any of those here?

[Paul Komski]

Some indication of what the "conference" would be about would be nice and perhaps the programming forum would be an appropriate place for it.

[yawningdog]

Alrighty then. Here's the code
Sub Test1()

Sheets("SING PLY").Select
Dim row As Integer
row = 31
Dim column As Integer
column = 2
If Sheet2.Cells(row, column) <> 0 Then
Sheet2.Range(Cells(31, 1), Cells(31, 6)).Select
Selection.copy
Sheets("Buy Out").Range(Cells(31, 1), Cells(31, 6)).Select
'Range(Cells(31, 1), Cells(31, 6)).Select
ActiveSheet.Paste

End If

End Sub


The idea is that it's supposed to examine a cell for a value other than zero. If it finds a valid value, it copies the range from the original sheet (sheet2 in this case) to another sheet (called "buy out"). When I run the macro, I get the following error.

Run-time error '1004'
Application-defined or object-defined error.

Is there a VBA guru that can tell me why this code isn't running? I can attach the file if need be.

Bear in mind, this is eventually going to run as a loop, and I'm using the "Cells" object so that I can increment the values in the ranges.

[Paul Komski]

I've done quite a bit with VB6 and VBA but with Access and not Excel so I'm not too familiar with the Excel syntax.

The error seems to arise either because you are referencing a non-active sheet at that moment and/or because there seems to be something strange when referencing some sheets using the cell or range with numerical rather than string references. Don't ask my why. I could get the code to work however with what follows. There is bound to be a correct way of referencing numerically with row and column integers but I couldn't suss it out for the moment anyway. To get any loops, etc going you would need to use a bit of string manipulation with the code as presented.

Sub Test1()

Dim StartRange As String, EndRange As String
StartRange = "A31"
EndRange = "F31"

If Sheets("SING PLY").Range(StartRange) <> 0 Then
Sheets("SING PLY").Activate
Sheets("SING PLY").Range(StartRange & ":" & EndRange).Select
Selection.Copy
Sheets("Buy Out").Activate
Sheets("Buy Out").Range(StartRange).Select
ActiveSheet.Paste
Else

End If

End Sub

[Paul Komski]

The following is I think more elegant and doesn't require all the activating and selecting.

Sub Test2()

Dim sourceRange As Range
Dim destrange As Range

If Worksheets("SING PLY").Range("A31") <> 0 Then
Set sourceRange = Worksheets("SING PLY").Range("A31:F31")
Set destrange = Worksheets("Buy Out").Range("A31")
sourceRange.Copy destrange
End If

End Sub


or if you prefer


Sub Test2()

Dim sourceRange As Range, destrange As Range
Set sourceRange = Worksheets("SING PLY").Range("A31:F31")
Set destrange = Worksheets("Buy Out").Range("A31")

If Worksheets("SING PLY").Range("A31") <> 0 Then sourceRange.Copy destrange

End Sub

[yawningdog]

Yeah, I actually had it working with the ranges defined that way, but I can't get a loop to work unless I increment the values somehow, and the only way I can find to do that is with the Cells object. I'm pretty sure it really will work that way and that I just need some coaching on the syntax.

[Paul Komski]

The following all works for me. You can use numerical if you like or call the function to translate into Cell Strings.

Make sure it is all in the Workbook and not a Sheet module.

Using sourceRange.Copy is more elegant and faster since it is not having to do a repeated selection and copy and paste. It's up to you of course.

Option Explicit

Public Sub Test1()

Dim intNumRows As Integer, intNumCols As Integer
Dim intBaseCol As Integer, intN As Integer
intBaseCol = 1
intNumRows = 31
intNumCols = 6

For intN = 1 To intNumRows
If Sheets("SING PLY").Cells(intN, intBaseCol) <> 0 Then
Sheets("SING PLY").Activate
Sheets("SING PLY").Range(Cells(intN, intBaseCol), Cells(intN, intNumCols)).Select
Selection.Copy
Sheets("Buy Out").Activate
Sheets("Buy Out").Cells(intN, intBaseCol).Select
ActiveSheet.Paste
End If
Next

MsgBox "The last valid cell was " & strColString(intNumRows, intNumCols)
End Sub


Public Function strColString(intRow As Integer, intCol As Integer) As String

If intCol < 1 Or intCol > (26 * 26) Then
MsgBox "Invalid input"
Exit Function
End If

Dim intMod As Integer, intInt As Integer, strCarry As String
intMod = intCol Mod 26
intInt = Int(intCol / 26)
strColString = ""
If intInt > 0 Then strColString = strColString & Chr(64 + intInt)
strColString = strColString & Chr(64 + intMod) & intRow

End Function

[Paul Komski]

Latest offering for a Worksheet Module

Option Explicit

Public Function strCell(Row As Integer, Col As Integer) As String
'CHECK INVALID INPUT
If Col < 1 Or Col > (26 * 26) Then
MsgBox "Invalid input"
Exit Function
End If
'CALCULATE CELL VALUES
Dim intMod As Integer, intInt As Integer
intMod = Col Mod 26
intInt = Int(Col / 26)
strCell = ""
If intInt > 0 Then strCell = strCell & Chr(64 + intInt)
strCell = strCell & Chr(64 + intMod) & Row
End Function

Public Sub TestIt()
'SET-UP VARIABLES
Dim sSheet As String, dSheet As String
Dim Rows As Integer, Cols As Integer
Dim BaseCol As Integer, N As Integer
Dim sourceRange As Range, destRange As Range
'ENTER RELEVANT DATA
BaseCol = 1
Rows = 31
Cols = 6
sSheet = "SING PLY"
dSheet = "Buy Out"
'LOOP
For N = 1 To Rows
Set sourceRange = Worksheets(sSheet).Range(strCell(N, BaseCol) & ":" & strCell(N, Cols))
Set destRange = Worksheets(dSheet).Range(strCell(N, BaseCol))
If Worksheets(sSheet).Range(strCell(N, BaseCol)) <> 0 Then sourceRange.Copy destRange
Next
End Sub

[Ruud]

Hey guys,

I've been struggling with the same thing for ages, but i just found out how you could make this work. The main piece of code for selecting (or doing something else to) a range on another worksheet is this:

With Worksheets("Sheet1")
.Range(.Cells(100, 10), .Cells(103, 15)).Font.Bold = True
End With

Mind the dots!!

This way you can keep on using the variables for the row- and column-numbers.



I guess (though i didn't check thouroughly) your code translates into

For intN = 1 To intNumRows
If Sheets("SING PLY").Cells(intN, intBaseCol) <> 0 Then
with Sheets("SING PLY")
.Range(.Cells(intN, intBaseCol), .Cells(intN, intNumCols)).Select
Selection.Copy
end with

with Sheets("Buy Out")
.Cells(intN, intBaseCol).Select
Selection.Paste
end with

End If
Next


I'm not sure about that second part, and I'm pretty sure that there's no need to use the 'with' statement-construction if selecting a CELL (instead of a RANGE) but you're a clever guy, play around with it a bit.

[Paul Komski]

The newly suggested code doesn't quite work and, having revisited everything, the recurring underlying problem seems much more related to what is active or where the code is placed rather than any particularly bad syntax - though some reading up on Ranges would seem wise; Range is not always the correct method. It seems that when any copy takes place that sheet must be either made active or only work "locally" at the time.

Using With and dot-operators just makes the code cleaner and easier to modify later but no difference to the output.

The following examples work for me and obviously variables can be exchanged. The basic line for a copy/paste operation of a range of cells seems to be:
[Worksheets(A).]Range(x).copy [Worksheets(B).]Cells(y)
Depending on the circumstances Worksheets(A) may need to be made active physically or programatically and particularly if the copy is to another sheet. If Worksheets(A) is the active sheet then the basic line to copy to another sheet can simply just be:
Range(x).copy Worksheets(B).Cells(y)
The If statement used in the examples should (as with the paste destination) be referenced by a single cell and not by a range.

EXAMPLES
To copy just six cells to another row within a sheet you could simply use:

Sub CopyToRow10()
Range(Cells(1, 1), Cells(1, 6)).Copy Cells(10, 1)
End Sub

If this code is in the intended sheet's module it will work from anywhere but only on that sheet. If it is in the "This Workbook" module it will work on whichever sheet is active at the time.

To copy just six cells from Sheet2 to Sheet3:

Sub CopyToSheet3_A1_A()
Worksheets("Sheet2").Activate
'The above line can be omitted if the macro is run from Sheet2
If Worksheets("Sheet2").Cells(1, 1) <> 0 Then
Worksheets("Sheet2").Range(Cells(1, 1), Cells(1, 6)).Copy Worksheets("Sheet3").Cells(1, 1)
End If
End Sub

or

Sub CopyToSheet3_A1_B()
With Worksheets("Sheet2")
.Activate
'The above line can be omitted if the macro is run from Sheet2
If .Cells(1, 1) <> 0 Then
.Range(Cells(1, 1), Cells(1, 6)).Copy Worksheets("Sheet3").Cells(1, 1)
End If
End Sub

[Paul Komski]

Just had an "of course" moment while sitting in the smallest room in the house. The answer to the problem is simple and that is to not use selections or copying at all. I might have got there quicker if I used excel much or practically at all.

Sub CopyRange()
Dim r, rr, c, cc, refcol
rr = 5
cc = 5
refcol = 1
With Sheets("Sheet2")
For r = 1 To rr
If .Cells(r, refcol) <> 0 Then
For c = 1 To cc
Sheets("Sheet3").Cells(r, c) = .Cells(r, c)
Next c
End If
Next r
End With
End Sub

[Ruud]

Ok,

The previous post might work (didn't check it), but for the greater good of a better understanding of VBA, there's a few things I found out about RANGE and CELLS objects that I'd like to share with you.

I found them out after playing around with it for a little more and after trying virtually every situation possible. I came to a conclusion that is quite simple, yet I had always been looking at it differently, and so have most of you guys, as the snippets show.

The thing is that it is IMPOSSIBLE to define a range that is not on the active sheet when it is done like this ("Sheet1" assumed to be the active one.)

set myrange = worksheets("Sheet2").range(cells(1,1),cells(2,2))

This gives an error, though it seems so logical. The reason is that a RANGE-object contains WITHIN it the worksheet it is defined on! It makes no sense to add 'worksheets("Sheet2")'. This goes both for when setting the range (as above), and for when using it at a later stage:

set myrange = range(cells(1,1),cells(2,2))
worksheets("Sheet2").myrange = "cheers mate"


However, the CELLS objects DONOT contain within them the worksheet. Therefore, the correct way of defining a range on another sheet is the following:

set myrange = range(worksheets("Sheet2").cells(1,1), worksheets("Sheet2").cells(2,2))

Now 'myrange' refers to the cells on "Sheet2", without having needed to select or activate it. When the 'worksheets("Sheet2").' before the 'cells' is omitted, VBA assumes you mean the currently active sheet.

It just came to me, and if you've been struggling with this as long as i have, you'll be very relieved! :)
I hope it's correct, btw :rolleyes:

RUUD

[Paul Komski]

If you want to use Ranges (which I actually managed to avoid with the last bit of code) then there are apparently three main ways:-

Referring to Ranges in Your VBA Code (http://j-walk.com/ss//excel/tips/tip20.htm)

But you are correct that if you use Range to define a block in the non-active sheet then it must be fully qualified and if you use the Cells method (to define the extent of that range) then they too must be fully qualified unless that sheet is the active sheet.

[yawningdog]

I'm afraid I'm still lost. I don't understand what it means to make a sheet "active" or how to do so or why. I REALLY don't know anything about VBA and trying to learn from scratch is an uphill battle. The syntax has me a little confused as well. It seems like you can reference a sheet by its name (sing ply) or its number (sheet2). I'm not sure to what extent if any these two references are interchangeable.

It's a project I'm working on as holes in my schedule open up, so I apologize for the delay.

[Paul Komski]

It seems like you can reference a sheet by its name (sing ply) or its number (sheet2)
Actually there are a number of ways to identify a sheet in the Sheets collection by what is placed in the () after Sheets.

The original name eg Sheets("Sheet2") or a renamed name eg Sheets("Sing Ply") both of which are strings and even just Sheets(2) which refers to the second sheet from the left of the spreadsheet. Which one to use only depends on how you might want to assign variables, use other functions or loop through a collection.

The Active sheet is effectively the sheet which is currently in use. It can be the one you switch to manually or you can activate it programatically.

The reason why is that some operations such as copying or indeed just making a selection can only be done to the active sheet.