With/End With statement without the dot

M

mark91345

Board Regular
Joined
Feb 11, 2011
Messages
113
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
[TABLE="class: head"]
<tbody>[TR="bgcolor: #E0E0F0"]
[TH][/TH]
[TH]
A​
[/TH]
[TH]
B​
[/TH]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #E0E0F0"]
6​
[/TD]
[TD="bgcolor: #548235"]Company[/TD]
[TD="bgcolor: #548235"]Manager[/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #E0E0F0"]
7​
[/TD]
[TD]Jellyfish[/TD]
[TD]Camila Wilson[/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #E0E0F0"]
8​
[/TD]
[TD]Dasring[/TD]
[TD]Greg Earnest[/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #E0E0F0"]
9​
[/TD]
[TD]Fightrr[/TD]
[TD]Walter Jackson[/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #E0E0F0"]
10​
[/TD]
[TD]Kryptis[/TD]
[TD]Margaret Rivera[/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #E0E0F0"]
11​
[/TD]
[TD]Perino[/TD]
[TD]Eddie Chen[/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #E0E0F0"]
12​
[/TD]
[TD]Twistrr[/TD]
[TD]Rachel Crosby[/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #E0E0F0"]
13​
[/TD]
[TD]Hackrr[/TD]
[TD]Anthony Welch[/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #E0E0F0"]
14​
[/TD]
[TD]Pes[/TD]
[TD]Norma Ashley[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="class: grid"]
<tbody>[TR]
[TD]Sheet: Activity[/TD]
[/TR]
</tbody>[/TABLE]
 
Last edited:

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.
Sort by date Sort by votes
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:
Upvote 0
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?
Click to expand...

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 to expand...

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:
Upvote 0
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
[Table="width:, class:head"][tr=bgcolor:#E0E0F0][th] [/th][th]
A​
[/th][th]
B​
[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
6​
[/td][td=bgcolor:#548235]Company[/td][td=bgcolor:#548235]Manager[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
7​
[/td][td]Jellyfish[/td][td]Camila Wilson[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
8​
[/td][td]Dasring[/td][td]Greg Earnest[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
9​
[/td][td]Fightrr[/td][td]Walter Jackson[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
10​
[/td][td]Kryptis[/td][td]Margaret Rivera[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
11​
[/td][td]Perino[/td][td]Eddie Chen[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
12​
[/td][td]Twistrr[/td][td]Rachel Crosby[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
13​
[/td][td]Hackrr[/td][td]Anthony Welch[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
14​
[/td][td]Pes[/td][td]Norma Ashley[/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Sheet: Activity[/td][/tr][/table]
 
Upvote 0
In what way doesn't your code work? It works for me.
 
Upvote 0
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.
 
Upvote 0
mikerickson said:
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.
Click to expand...


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.
 
Upvote 0
You must log in or register to reply here.

Similar threads

C
  • Question Question
Running VBA Code from Excel to delete a Rich Text Format from a Word Document Dependent on A Dropdown Box in Excel
Replies
4
Views
1K
Dan_W
D
I
  • Solved
Edited code now i get End With Without With error
Replies
5
Views
548
MARK858
MARK858
R
  • Question Question
Unable to save date in csv file if date is greater than 10
Replies
4
Views
902
DanteAmor
DanteAmor
T
  • Question Question
Adapting Old VBA Code for PDF Printing
Replies
0
Views
758
treaves04
T
A
  • Question Question
Creating a 1D list from a 2D list and looping it through the entire workbook
Replies
1
Views
564
akatz821
A

Forum statistics

Threads
1,223,903
Messages
6,175,284
Members
452,630
Latest member
OdubiYouth

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top