Help with the code

[ismokenov1991]

Hey there

So I have just started learning vba by reading watching etc.

there is this interesting code that i have saw on the internet but I dont understand some lines hope you could explain to me, it might be actually easy but i am confused with some lines.


Here is it

Private Sub TrSheet ()
Dim CustomerName As String, CustomerProblem As Integer
    Worksheets("Sheet1").Select
    CustomerName = Range("C4")
    CustomerProblem = Range("C5")
    Worksheets("sheet2").Select
    Worksheets("sheet2").Range("B4").Select
    If Worksheets("sheet2").Range("B4").Offset(1, 0) <> "" Then
      Worksheets("Sheet2").Range("b4").End(xlDown).Select
    End If
ActiveCell.Offset(1, 0).Select
ActiveCell.Value = CustomerName
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = CustomerProblem
 
Worksheets("sheet1").Select
Worksheets("sheet1").Range("C4").Select
   
End Sub

I have really huge problem understanding how is the IF statement used here, especially with the offset function and "<>". From what I see, if the selected cell has value(number or word) then is should go down one row is it?

I would really appreciate if someone could explain it to me or gave links to the places where people have had similar problems with this functions.


And yes, this sub is meant to transport data from one sheet to another.


Thanks
Isken

 

[Luke M]

Welcome to the forum! I've tried to explain each of the lines for you.

Private Sub TrSheet()
'Define our 2 variables
Dim CustomerName As String, CustomerProblem As Integer
    
    'Activate a worksheets
    Worksheets("Sheet1").Select
    
    'Assign values to the 2 variables
    CustomerName = Range("C4")
    CustomerProblem = Range("C5")
    
    'Select another worksheet
    Worksheets("sheet2").Select
    'Select a range
    Worksheets("sheet2").Range("B4").Select
    
    'The Offset method lets you give 2 optional arguments, stating how many rows/columns
    'that you would like to move from the previous object. The numbers can be postive to
    'go down and to the right, or negative to go up or to the left
    'This line is refering to 1 cell below range B4, aka B5. Not sure why the
    'original author didn't just write B5...
    
    'Anyway, the If statements checks if cell B5 is not blank, and then...
    If Worksheets("sheet2").Range("B4").Offset(1, 0) <> "" Then
        '...It finds that last cell that has something in it that is below(down) from
        'cell B4. This is the same as hitting Ctrl+down as a keyboard shortcut
      Worksheets("Sheet2").Range("b4").End(xlDown).Select
    End If
 
'The active cell is now whatever we found from If statement, or if B5 was blank,
'it will be B4
ActiveCell.Offset(1, 0).Select
'Make that cell this value
ActiveCell.Value = CustomerName
'and the cell to the right (offset column +1)...
ActiveCell.Offset(0, 1).Select
'Becomes this value
ActiveCell.Value = CustomerProblem
 
 'Select some more stuff
Worksheets("sheet1").Select
Worksheets("sheet1").Range("C4").Select
   
End Sub

 

[Luke M]

If you want further reading/study, the following macro does the same thing, but is a little more efficient. It does all the work w/o having to select sheets/cells. Just some extra food for thought.

Private Sub TrSheet()
'Define our 2 variables
Dim CustomerName As String, CustomerProblem As Integer
    
'work with this sheet, rather than selecting, which takes extra work
With Worksheets("Sheet1")
    'Assign values to the 2 variables
    CustomerName = .Range("C4")
    CustomerProblem = .Range("C5")
End With
 
 
'working with this sheet...
With Worksheets("sheet2")
 
    'Since we want the last blank cell, we could start at the bottom
    'of the worksheet and go up
    With .Cells(.Rows.Count, "B").End(xlUp)  'Cells method used to find last cell in col B
        'We define our next 2 cells from that reference point
        .Offset(1, 0).Value = CustomerName
        .Offset(1, 1).Value = CustomerProblem
    End With
End With
 
   
End Sub

 

[ismokenov1991]

Hey Luke

Thanks for the help here, I think now I have a better clue of what the code is doing bow.

And yes your code looks a little more easier to read.


Thanks
isken

 

[ismokenov1991]

If you want further reading/study, the following macro does the same thing, but is a little more efficient. It does all the work w/o having to select sheets/cells. Just some extra food for thought.

Private Sub TrSheet()
'Define our 2 variables
Dim CustomerName As String, CustomerProblem As Integer
   
'work with this sheet, rather than selecting, which takes extra work
With Worksheets("Sheet1")
    'Assign values to the 2 variables
    CustomerName = .Range("C4")
    CustomerProblem = .Range("C5")
End With
 
 
'working with this sheet...
With Worksheets("sheet2")
 
    'Since we want the last blank cell, we could start at the bottom
    'of the worksheet and go up
    With .Cells(.Rows.Count, "B").End(xlUp)  'Cells method used to find last cell in col B
        'We define our next 2 cells from that reference point
        .Offset(1, 0).Value = CustomerName
        .Offset(1, 1).Value = CustomerProblem
    End With
End With
 
 
End Sub

Hey Luke

Just finished studying the code, thanks.

The With statement is more easier and I dont have to refer to the object multiple times


Thanks alot
Isken

 

[Luke M]

Glad you liked it.

 

[Smallman]

Now that Luke has shown you how to do this task, then taken it one step further and shown it more efficiently (i like the use of comments, very helpful). Here is my take on getting the data into sheet2 in the same manner;

You can refer directly to the sheeet index number which covers you if the sheet name changes;

Sheet2 'your code here

Also the following method will offset one row without explicitly using offset.

Sub TrSheet1()
    Sheet2.Cells(Rows.Count, 2).End(xlUp)(2).Value = Sheet1.[C4]
    Sheet2.Cells(Rows.Count, 3).End(xlUp)(2).Value = Sheet1.[C5]
End Sub

Hope this adds to your learning.

Take care

Smallman

 

[Luke M]

You can refer directly to the sheet index number which covers you if the sheet name changes

A small clarification. The index number of a worksheet refers to the physical position of the worksheet within your workbook. So, if you want to select the 2nd sheet in your workbook, the code is:
Sheets(2).Select

The VBA name for a worksheet is the name that the VBA Project assigns to each worksheet. This can be set from the properties window, and all names can not have a space in them. By default, the code name is the same as the original worksheet name, aka "Sheet1", and can be used as Smallman demonstrated, and is helpful when the user may change the sheet names on you.
Sheet2.Select

So, lots of different ways to select a worksheet!

 

[Smallman]

Thanks for the clarification Luke. I always thought it was the sheet index number but it is the Sheet Object number. Of the three methods I know of;

1. sheet1.select
2. Sheets("Sheet1").select
3. Sheets(1).select


Only method 1 is robust enough to save you from the sheet name changing (as you mentioned). While there are many ways to select a worksheet, in my opinion option 1 is the best of them. You may not always be the custodian of aparticular spreadsheet and using this method, in a way, future proofs your coding.

Take care

Smallman

 

[Marc L]

Hi,​

​

Smallman, you're right for the sheet object, in fact not a number but its CodeName,​

but not when Option Explicit is used ! I recently discover this issue in an European Excel version …​