With/End With statement without the dot

mark91345

Board Regular
Joined
Feb 11, 2011
Messages
110
Using Excel 365, my VBA works, but I am curious about the With/End with section. I always assumed that I needed a "." to precede my entries within a With/End With statement. Why doesn't my code have the same need for a "dot" to precede the code? (I had to look online for some help with this and found that the code that worked did not have a dot). But why?

Lastly, is there a way to post code that includes the proper tabbing? This site removed my formatting when I pasted it in.



Sub With_No_Dot()
Dim CompInfo(1 To 8, 1 To 2)
Dim r As Byte, c As Byte
Const StartRow As Long = 6

Dim NewBook As Workbook
Dim ShNew As Worksheet

For r = 1 To 8
For c = 1 To 2
CompInfo(r, c) = Cells(StartRow + r, c).Value
Next c
Next r

Set NewBook = Workbooks.Add
For r = 1 To 8
With NewBook
Set ShNew = .Sheets.Add
ShNew.Name = CompInfo(r, 1)
Range("A1").Value = CompInfo(r, 1)
Range("A2").Value = CompInfo(r, 2)
End With
Next r
End Sub



Excel 2016 (Windows) 64 bit
A
B
6
CompanyManager
7
JellyfishCamila Wilson
8
DasringGreg Earnest
9
FightrrWalter Jackson
10
KryptisMargaret Rivera
11
PerinoEddie Chen
12
TwistrrRachel Crosby
13
HackrrAnthony Welch
14
PesNorma Ashley

<tbody>
</tbody>
Sheet: Activity

<tbody>
</tbody>
 
Last edited:

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
22,772
With out the dot, the line Range("A1") assumes the default, i.e. the ActiveSheet.
Your code adds a new sheet to NewBook.
That makes the new sheet the ActiveSheet.
So they are equivelent.

Were you to add a dot to the Range("A1") line, it would error, since Range is a property of a worksheet object, but not a workbook object.
Full qualification would look like

Code:
Set NewBook = Workbooks.Add
For r = 1 To 8
    With NewBook
        With .Sheets.Add
            .Name = CompInfo(r, 1)
            .Range("A1").Value = CompInfo(r, 1)
            .Range("A2").Value = CompInfo(r, 2)
        End With
    End With
Next r
 
Last edited:

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
4,605
Office Version
365
Platform
Windows
Using Excel 365, my VBA works, but I am curious about the With/End with section. I always assumed that I needed a "." to precede my entries within a With/End With statement. Why doesn't my code have the same need for a "dot" to precede the code? (I had to look online for some help with this and found that the code that worked did not have a dot). But why?
Yes you must ALWAYS use the DOT to be sure you get the correct result
The code did what you wanted because (in this case) the ACTIVE workbook happened to be the one you wanted to refer to
But that would not always be the case

The location of the code also matters
- if the above code was in ThisWorkbook module (rather than a standard module) Sheets.Add would have inserted the sheet in the wrong workbook (even though the new workbook was active at the time)
- Range("A5") refers to the active sheet in the active workbook when code is in a standard module
- Range("A5") refers to A5 in Sheet1 when code is in Sheet1's SHEET module
- using With Sheets("Sheet2") means that .Range("A5") refers to A5 in Sheet2

is there a way to post code that includes the proper tabbing? This site removed my formatting when I pasted it in.
Click on # icon above post window
These things appear (code tags)
[ CODE][ /CODE]
[ CODE] paste code here between the tags [ /CODE]

and this happens
Code:
Sub With_No_Dot()
    Dim CompInfo(1 To 8, 1 To 2)
    Dim r As Byte, c As Byte
    Const StartRow As Long = 6
    
    Dim NewBook As Workbook
    Dim ShNew As Worksheet
    
    For r = 1 To 8
        For c = 1 To 2
            CompInfo(r, c) = Cells(StartRow + r, c).Value
        Next c
    Next r
    
    Set NewBook = Workbooks.Add
    For r = 1 To 8
        With NewBook
            Set ShNew = .Sheets.Add
            ShNew.Name = CompInfo(r, 1)
            Range("A1").Value = CompInfo(r, 1)
            Range("A2").Value = CompInfo(r, 2)
        End With
    Next r
End Sub
OR paste code into the post window first, select the code and then click on # icon above post window which places the code tags around the selected text
 
Last edited:

mark91345

Board Regular
Joined
Feb 11, 2011
Messages
110
Ah, so I have a nested With statement. I get it.

I am curious, I Dim'd a variable, "ShNew as Worksheet", that I would like to use within the With statement to add sheets, but I cannot figure out HOW to employ it.
For example, in the With statement,
With NewBook
With ShNew (then add sheets code here)

I have tried multiple permutations, but nothing works so far. Frankly, your code is better (and simpler), but I need to understand how to work with variables in the With statements (in addition to the raw code).



Code:
Dim CompInfo(1 To 8, 1 To 2)Dim r As Byte, c As Byte
Const StartRow As Long = 6


Dim NewBook As Workbook
Dim ShNew As Worksheet


    For r = 1 To 8
        For c = 1 To 2
            CompInfo(r, c) = Cells(StartRow + r, c).Value
          Next c
    Next r
    
Set NewBook = Workbooks.Add
    For r = 1 To 8
             With NewBook
                With .Sheets.Add
                    .Name = CompInfo(r, 1)
                    .Range("A1").Value = CompInfo(r, 1)
                    .Range("A2").Value = CompInfo(r, 2)
                End With
            End With
    Next r
End Sub

Excel 2016 (Windows) 64 bit
A
B
6
CompanyManager
7
JellyfishCamila Wilson
8
DasringGreg Earnest
9
FightrrWalter Jackson
10
KryptisMargaret Rivera
11
PerinoEddie Chen
12
TwistrrRachel Crosby
13
HackrrAnthony Welch
14
PesNorma Ashley
Sheet: Activity
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
31,910
Office Version
365
Platform
Windows
In what way doesn't your code work? It works for me.
 

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
22,772
You could use the ShNew variable like this
Code:
    Set NewBook = Workbooks.Add
    For r = 1 To 8
             With NewBook
                Set ShNew = .Sheets.Add
                With ShNew
                    .Name = CompInfo(r, 1)
                    .Range("A1").Value = CompInfo(r, 1)
                    .Range("A2").Value = CompInfo(r, 2)
                End With
            End With
    Next r
End Sub
But it isn't needed for this task.
 

mark91345

Board Regular
Joined
Feb 11, 2011
Messages
110
You could use the ShNew variable like this
Code:
    Set NewBook = Workbooks.Add
    For r = 1 To 8
             With NewBook
                Set ShNew = .Sheets.Add
                With ShNew
                    .Name = CompInfo(r, 1)
                    .Range("A1").Value = CompInfo(r, 1)
                    .Range("A2").Value = CompInfo(r, 2)
                End With
            End With
    Next r
End Sub
But it isn't needed for this task.

Yes, I now see that it's redundant, but at least I know HOW to add it it within the WITH statement.

Thank you for your help.
 

Forum statistics

Threads
1,081,416
Messages
5,358,544
Members
400,503
Latest member
RedSquirrel

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...
Top