Newbie Questions (Cells, Ranges, Values, etc) 1

[rudejohn]

Background: I'm a decent Java and C programmer but rather new to VBA programming and having some definite trouble understanding the concepts. The tutorial(s) I've found have been absolutely dreadful. If anyone has suggestions for a good place to start, that's great.

Next, how do the ranges work? Example: I need to write a function that passes in a column number. I then format, calculate, and copy data TO that column from another column in the worksheet. Some crude sample code:

Sub myFunction(colNum As Integer)

    If colNum = 0 Then Exit Sub
    
    Dim c As Excel.Range
    
    ' Unsuccessfully trying to grab cell based on column number. If you pass in "3", I want to capture cell/range "C2" to manipulate it later

    Dim temp As String
    temp = ColNo2ColRef(colNum) & "2"
    c = Range(temp)
  
    ' Then I'd like to insert a formula into the cell, based on the range that was selected earlier
    temp = Month(c) & "/" & Day(c) & "/" & Year(c)
    Cells(2, colNum + 10).Value = Month(currentTop) & "/" & Day(currentTop) & "/" & Year(currentTop)
    
End Sub

I'm getting myriad "object required" and other such generic errors. I'm sure it's due to a lack of understanding of the basic concepts, but some clarity would be greatly appreciated. How does .Value work? What about Range objects?

TIA,
RJ

************
RudeJohn
************

 

[PHV]

As you defined c as an object (Excel.Range) you must use the Set instruction to instantiate it.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[rudejohn]

Thanks... first problem solved!

Any recommendations for a general "overview" of VBA or tutorial? All the Google results I got were just compilations of Excel files without much assistance inside...

************
RudeJohn
************

 

[mtb1996]

Hello rudejohn... back when I was first getting started writing VBA in Excel, I would record a macro doing what I wanted my VBA code to do. This creates a module in your spreadsheet and builds the VBA code for you. It rough and not very clean code and usually has more steps than you really need to take, but it's a great way to start to get familiar with VBA, it objects, properties, and methods.

 

[rudejohn]

Thanks, that's what I tried to do. But I'm having a bit of trouble translating hard values to variables. For example, if the code says:

SomeFunction(A1)

How do I change that to

Dim i As String
i = colNum & rowNum
SomeFunction(i)

The code above doesn't seem to work in some cases. Furthermore, what about the same situation when it's a RANGE? How do I *create* range and cell objects to work with? How can I iterate through each cell in a column that has data and manipulate the formulas inside? These things are tough to do manually, so it's hard to see how to record them and then play with it...

Thanks,

RJ

************
RudeJohn
************

 

[PHV]

How can I iterate through each cell in a column that has data and manipulate the formulas inside?
A starting point:
For Each c In Intersect(ActiveSheet.UsedRange, ActiveSheet.Columns(colNum))
Debug.Print c.Address, c.Value, c.Formula
Next

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[xlbo]

You 1st need to understand the excel object model. To view this, press F2 whilst in the VBE. From there, you can get to help files for each object with all its properties and methods..

 

[mtb1996]

When I've written macros to automate simple but mundane tasks... and I don't want to hard code the cell references, I prefer to use "Cells" instead because the reference for the column is numeric, not a letter. For example, cell D5 would be Cells(5,4).SomePropertyOrMethod.

Another way to iterate through a column is like this:

Dim obj As Object
For Each obj In Range("B:B").Cells
'Do something here
Next obj

 

[xlbo]

This is where an understanding of the object model would help

cells>range>worksheet>workbook>application

 

[rudejohn]

Excellent help... thanks PHV. I'm having an additional problem now. I'm trying to grab a date from one field and either remove the time from it, or copy it to a new field with the time removed. Example:
C2 has the value "1/6/2005 8:00AM"
I want to have the value "1/6/2005" without the time after it. I'm not talking about *display* (which could be handled by formatting): I need the actual data to not contain a particular hour. Here's what I came up with, but I'm getting a "type mismatch" on the Month() formula.

    For Each c In Intersect(ActiveSheet.UsedRange, ActiveSheet.Columns(colNum))

        Set cOld = c
        Set cNew = c.Offset(0, 10)

        cNew.Value = Month (c) & "/" & Day (c) & "/" & Year(c)
        Debug.Print c.Address, c.Value, c.Formula
        
    Next

As you can see, I tried casting c to an Excel.Range (cOld)
The Month() function works fine if I put Month(A1), but it doesn't work at all with any of the following:
Month(c)
Month(cOld)
Month(c.Address)
Month(c.Value)

Any assistance?

xlbo I'll study up on the object model, but I'm sure I'll still have specific syntax questions like this.

Thanks,

RJ

************
RudeJohn
************

 

[PHV]

You may try this:
c.Offset(0, 10).Value = CDate(Left(c.Text, InStr(c.Text, " ") - 1))

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[rudejohn]

Excellent. I added some safeguards to prevent it from being zero, and safeguards to ignore the header row, and this formula works perfect. I owe ya one!

~John

************
RudeJohn
************

 

[DaveInIowa]

Since a date data type is simply stored as a number with the integer portion representing the day and the decimal portion the time, you can remove the time portion by:

cNew = Int(c)

 

[DaveInIowa]

Or more straightforward:

cNew = DateValue(c)