Free Microsoft Excel 2013
Quick Reference
Free Microsoft 2013 Quick Reference Guide

Free Microsoft Excel 2013 Quick Reference

VBA Constant

Is there any way that I can change a constant in VBA

Option Explicit
Const MaxAccuracy = 0.00001
Const MaxLoops = 100
Constant Reset = 900

I want to be able to change the Reset constant to 900 or 3600.

The variable is called over 20 times within the code so if possible I would rather use the Option Explicit rather than make all of the entries using Public Function for each area of the module.

Any help would be greatly appreciated - thanks

edvwvw


Post your answer or comment

comments powered by Disqus
Is there a book or online reference that contains a list of the values of
predefined VBA constants for Excel (e.g. xlDown, xlPasteValues)?

Hello,

I am fairly experenced excel user but I am relatively new to VBA (only started using it a couple of weeks ago).

I am currently trying to build a engineering spreadsheet for the design of foundations. this requires alot of functions and calculations.

What i am looking to do is use the spreadsheet as an input and output area only and carry out all the calculations in VBA so i want to refer to the spreadsheet as little as possible in the code.

My Idea to do this was to create a module with all the input parameters for the calculations that are required which would pick the values of the parameters from the values entered into the spreadsheet cells, these parameters could then be used throughout the programme code without having to refer back to the spreadsheet again.

One way I this this was expressing each parameter as a funtion e.g
Function dia()
dia=Range("dia").value
End function
This works but seems to stop the spreadsheet updating automatically.

Ive tried using varibale and constants but theu dont allow you to refer to the spreadsheet.

I hope someone can help, this has been annoying me for the last couple of days

Thanks
Ian

HI There

For a few weeks now, we have been using an excel form which users fill in and then click a submit button which automatically emails the completed form to us. Until now this has worked perfectly. However, I am now running into problems as we are in the midst of an upgrade to Office 2010. Some users are still using Office 2003. When they try to send the email they are getting an error as it is looking for the outlook14 object library.

I googled a bit and tried changing my code to use late binding. However, I am getting an error message when I run the code. The code works fine until it gets to the .To line when it says Run Time error 438 object does not support this method.

Can anyone help

Sub Outlookmessage()
Const OLMAILITEM = 3    ' Outlook VBA constant olMailItem
Dim OutlookApp As Object
Dim MyItem As Object
Dim EmailAddr As String
Dim ccAddress As String
Dim Subj As String
Dim Sender As String
Dim Msg As String
    Set OutlookApp = CreateObject("Outlook.Application")
    
    Set MyItem = OutlookApp.CreateItem(OLMAILITEM)
    
    EmailAddr = "elise.freedman@baesystems.com"
    'ccAddress = UserForm1.Label2.Caption
    Subj = "SHE Improvement Suggestion"
        
    Msg = "Please find attached a SHE Improvement Suggestion" & vbCrLf & vbCrLf
  
   
    
    With MyItem
        .To = EmailAddr
                   
        .Subject = Subj
        .Body = Msg
  .Attachments.Add myfile2
   .Attachments.Add myfile
.Send
    End With
    
 End If
 End If


Using Mac, OS X 10.3.9, Excel X for Mac.

I need to write a text file in VBA with a Unix line break (ASCII 10 / x0A).
I'm iterating over a range of cells and writing out lines with:

Print #FileNum, Cell.Text & Chr(10)

Anyway, when I get my file written out, it's not performing as I expected. I
did a hex dump with TextWrangler and found that I'm not getting a linefeed
character written out. Here's a sample hex line:

0050: 00 0D 00 0D 00 64 00 69 00 67 00 20 00 31 00 39 .....d.i.g. .1.9

The first two characters should be a single line feed (x0A), but I'm getting
a double carriage return (x00 x0D).

Two questions:
1. It looks like Excel is putting out double-byte ASCII (all the extra 00 in
front of the actual ASCII characters). Is this correct?

2. Why am I getting ASCII 13 / x0D when I'm explicitly writing out ASCII 10
/ x0A?
-------------------

P.S. I discovered that some common Mac VBA constants are not as expected:
vbCr 13
vbCrLf 13
vbFormFeed 12
vbLf 13
vbNewLine 13
vbNullChar 0
vbObjectError 45
vbTab 9

2003

Would like to create a w/s that has a listing of Font constants like
-4105 and Fill like 8 that are applicable to my workbook as I thing
that the data follows th book?.

Once that I have that list I then will do a Vlookup to populate another
worksheet.

Thanks,

EagleOne

I have a column with US phone numbers (all numerical, 10 digits). I would
like to have the State name in a column right next to the phone number, and
the Time Zone in the next column to the right.

I have the master Area Code, State, Time Zone data in any format needed: one
long string, range, VBA constant...

The obvious solution is to write a VBA that uses the 3 left-most digits and
scans the master area-code table to find a match. The only problem is that my
list of phone numbers has several 10,000 numbers and scanning the master area
code again and again will take hours...

Any advanced idea anyone?

--
MF
Boston

This script works, mostly.

I am using the following macros to plot data for an experiment. The macros do work, it's just that my AddChartSheet() macro doesn't work entirely as intended (WS Incorrect_Chart) and requires some clean up (WS Correct_Chart). I could add more code to remove the offending output, but I'd rather know where I made a mistake in my code.

I start with a fresh workbook and evoke NameColumns() and SubtractResiduals(). I then import data from a file (20120203_Si_pwdr1t5m_27..dat), which is a spaced-separated value file. Next, I evoke AddChartSheet(). The macro creates a chart as seen in Work Sheet "Incorrect_Chart" and I have to edit it to look like Work Sheet "Correct_Chart".

Sub NameColumns()
'
' NameColumns Macro
' This will insert column headers for 2theta, Intensity, Kalpha1, Kalpha2, Background, Model, Residuals, and Residuals
Subtracted, as well as, the fityk counterpart names.
'

'
    Range("A1").FormulaR1C1 = "2?"
    Range("B1").FormulaR1C1 = "Intensity"
    Range("C1").FormulaR1C1 = "F[0]"
    Range("D1").FormulaR1C1 = "F[1]"
    Range("E1").FormulaR1C1 = "F[2]"
    Range("F1").FormulaR1C1 = "F[x]"
    Range("G1").FormulaR1C1 = "y-F[x]"
    Range("H1").FormulaR1C1 = "y-F[x])"
    Range("H1").FormulaR1C1 = "y-F[x]-max(::g3::)"
    Range("A2").FormulaR1C1 = "2?"
    Range("B2").FormulaR1C1 = "Intensity"
    Range("C2").FormulaR1C1 = "ka1"
    Range("D2").FormulaR1C1 = "ka2"
    Range("E2").FormulaR1C1 = "Background"
    Range("F2").FormulaR1C1 = "Model"
    Range("G2").FormulaR1C1 = "Residuals"
    Range("H2").FormulaR1C1 = "Residuals Subtracted"
End Sub
Sub NameTestValueCells()
    Range("K1").FormulaR1C1 = "ka1max"
    Range("K2").FormulaR1C1 = "ka2max"
    Range("K3").FormulaR1C1 = "ka1pos"
    Range("K4").FormulaR1C1 = "ka2pos"
    Range("K5").FormulaR1C1 = "ka1center"
    Range("K6").FormulaR1C1 = "ka2center"
    Range("K7").FormulaR1C1 = "kaSeparation"
    Range("K8").FormulaR1C1 = "leftoverDist"
    Range("K9").FormulaR1C1 = "leftoverSplit"
    Range("K10").FormulaR1C1 = "X-Min"
    Range("K11").FormulaR1C1 = "X-Max"
End Sub
Sub SubtractResiduals()
'
' SubtractResiduals Macro
' Creates a function, based on Residuals, to subtract 2*Max(Residuals) value from Residuals.
'

'
    Range("H3").FormulaR1C1 = "=RC[-1]-2*MAX(R3C7:R2002C7)"
    Range("H3").Select
    Selection.AutoFill Destination:=Range("H3:H2002")
    Range("H3:H2002").Select
End Sub
Sub ResizeColumnWidths()
'
' ResizeColumnWidths Macro
' Balances widths of columns to increase readability.
'

'
    Columns("A:A").EntireColumn.AutoFit
    Columns("B:B").EntireColumn.AutoFit
    Columns("C:C").EntireColumn.AutoFit
    Columns("C:C").EntireColumn.AutoFit
    Columns("D:D").EntireColumn.AutoFit
    Columns("E:E").EntireColumn.AutoFit
    Columns("F:F").EntireColumn.AutoFit
    Columns("G:G").EntireColumn.AutoFit
    Columns("H:H").EntireColumn.AutoFit
End Sub

'Common Excel Chart Types
'Chart      VBA Constant (ChartType property of Chart object)
'Column xlColumnClustered, xlColumnStacked, xlColumnStacked100
'Bar xlBarClustered, xlBarStacked, xlBarStacked100
'Line       xlLine, xlLineMarkersStacked, xlLineStacked
'Pie xlPie, xlPieOfPie
'Scatter xlXYScatter, xlXYScatterLines

Public Sub AddChartSheet()
    Charts.Add   'Create a chart sheet
    
    With ActiveChart    'Set chart properties
        .ChartType = xlXYScatter
        .HasLegend = True
'        .Legend.Position = xlRight

        .Axes(xlCategory).MinorTickMark = xlOutside
        .Axes(xlValue).MinorTickMark = xlOutside
            With .Axes(xlCategory)
                Dim rngA As Range, rngC As Range, rngD As Range
                Set rngA = Worksheets("Sheet1").Range("$A$3:$A$2002") 'rngA = $A$3:$A$2002 on Sheet 1
                Set rngC = Worksheets("Sheet1").Range("$C$3:$C$2002")
                Set rngD = Worksheets("Sheet1").Range("$D$3:$D$2002")
                
                Dim ka1center As Double, ka2center As Double
                'INDEX(range, ka_max position) -> INDEX(range, MATCH(ka_max, range, match type))
                ka1center = Application.WorksheetFunction.Index(rngA,
Application.WorksheetFunction.Match(Application.WorksheetFunction.Max(rngC), rngC, 0))
                ka2center = Application.WorksheetFunction.Index(rngA,
Application.WorksheetFunction.Match(Application.WorksheetFunction.Max(rngD), rngD, 0))
                
                Dim kaSeparation As Double
                kaSeparation = ka2center - ka1center
                Dim leftoverDist As Double
                leftoverDist = 1 - kaSeparation
                Dim leftoverSplit As Double
                leftoverSplit = leftoverDist / 2
                .MinimumScale = ka1center - leftoverSplit
                .MaximumScale = ka2center + leftoverSplit
                
                'Use above information to create "best" window on x-axis
                '
                '.MinimumScale = Range("Sheet1!$A$3").Value + 0.5
                '.MaximumScale = Range("Sheet1!$A$3").Value + 1.5
                
                
            End With
        .Axes(xlCategory).HasTitle = True
        .Axes(xlCategory).AxisTitle.Characters.Text = "2theta (degrees)" '"X-axis Labels"
        .Axes(xlValue).HasTitle = True
        .Axes(xlValue).AxisTitle.Characters.Text = "Intensity (counts)" '"Y-axis"
        .ChartTitle.Text = Range("Sheet1!$A$3").Value '"Chart Title"
    
    'Loops through series, setting ranges and names
    Dim nameArray As Variant
    Dim dashTypeArray As Variant
    Dim rngArray As Variant
    
    rngArray = Array("", "Sheet1!$B$3:$B$2002", "Sheet1!$C$3:$C$2002",
"Sheet1!$D$3:$D$2002", "Sheet1!$E$3:$E$2002", "Sheet1!$F$3:$F$2002",
"Sheet1!$H$3:$H$2002")
    nameArray = Array("", "Intensity", "K alpha 1", "K alpha 2",
"Background", "Model", "Residuals Subtracted")
    'dashTypeArray = Array("", "msoLineSquareDot", "msoLineLongDash", "msoLineDash",
"msoLineRoundDot", "msoLineSolid", "msoLineDashDot")
    'dashTypeArray = Array("", msoLineSquareDot, msoLineLongDash, msoLineDash, msoLineRoundDot, msoLineSolid,
msoLineDashDot)
    dashTypeArray = Array(0, 2, 7, 4, 3, 1, 5)
    'msoLineSolid    Line is solid.
    'msoLineSquareDot    Line is made up of square dots.
    'msoLineRoundDot Line is made up of round dots.
    'msoLineDash Line consists of dashes only.
    'msoLineLongDash Line consists of long dashes.
    'msoLineDashDot  Line is a dash-dot pattern.
    'msoLineLongDashDot  Line is a long dash-dot pattern.
    'msoLineDashDotDot   Line is a dash-dot-dot pattern.

    Dim I As Integer
    I = 1
    While I < 7
        With .SeriesCollection(I)
            .Name = nameArray(I)
            .Values = rngArray(I)
            .XValues = Range("Sheet1!$A$3:$A$2002")
            If StrComp(nameArray(I), "Intensity", 1) = 0 Then
                .Format.Line.Visible = msoFalse
                .MarkerStyle = xlMarkerStyleCircle
                .MarkerSize = 3
                .Format.Fill.Visible = msoFalse
            Else
                .MarkerStyle = xlMarkerStyleNone
                .Select
                With Selection.Format.Line
                    .Visible = msoTrue
                    .DashStyle = dashTypeArray(I)
                    .Weight = 1
                End With
            End If
        End With
        I = I + 1
    Wend
        
    End With
End Sub
WB_for_Excel_Programming_Forum.xlsm
Oddly, the attachment manager doesn't recognize my .dat file as a valid file type. Well, the data is in the WB, I just thought a raw source would have been nice for troubleshooting. Also, this is my first foray into extending Excel, so if I'm trying to do something haphazardly, let me know. :-)

Is there a VBA function that will return the string name of a vba
constant from its number?

x = f(-4108)

would return

xlVAlignCenter

Thanks

Fred Holmes

I've read the very helpful thread on using F5-Special-Constants-Clear Contents (so thank you again Dave Hawley!)- is there a way to do this in VBA?

I've also read:
- Dave Hawley's recommendation of using:

	VB:
	
Sheet1.UsedRange.SpecialCells(xlCellTypeConstants).ClearContents 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
from another thread (which is excellent!)

- SHG's recommendation of using a named range, for example:

	VB:
	
Range("Inputs").ClearContents 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
(which is a stellar idea)

Given my limited knowledge of VBA, how would I now combine the two to write a VBA sub-routine that clears a named range entitled "Entry" on a sheet entitled "Data"? Would the following be the correct syntax:

	VB:
	
Worksheets("Data").Range("Entry").SpecialCells(xlCellTypeConstants).ClearContents 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Your help (as always!) would be very much appreciated.

Let us say I have array constant in a1 as follows:
{1;2;3;4;5}

And suppose I want to store the above in an array using VBA...so

dim my_Array() as integer
my_array=range("a1").arrayFormula
msgbox my_array(1)

No way ...Can anyone explain why and how to make work.

I believe this is a valid approach to get each record from an external text file to an array inside a VBA macro ...Do you agree ?

Hi, does anybody know how I can refer to a public constant in a different workbook's (which is already open) VBA from the workbook which I am currently working on's VBA?

I have VBA where I want to define a cell address that I use throughout the
macro, as a constant up at the top. This makes the code neater, but more
importantly makes it easier to make global changes without forgetting something,
and in conjunction with "Explicit" makes it harder to mistype something. A
routine programming practice.

What I have used up to now, and which works well, is something like:

CONST TargetCell As String = "Sheet3!A1"

The thing is that I'm now using this particular macro from various places. As a
result, I'd like to set the CONST based on the active sheet name when the macro
is fist called. After setting the CONST it would not change of course.

I've tried variations on:

CONST TargetCell As String = ActiveSheet.Name & "!A1"

VBA apparently does not like to base a constant value on something that may
change later and refuses to cooperate. After a lot of futzing around I ended up
defining it as a string variable rather than a constant and VBA allows that.

Just as a matter of programming practice though I'd rather use a constant. Is
there some way to persuade VBA to create a constant based on the value of a
variable at the moment the constant is created?

Thanks...

Bill

You can define constants in VBA as

Public Const a=10

But how can you define a constant array? I need:

a(1)=1
a(2)=10
a(3)=5

or something like that.

Many thanks

Peter

I have a function in VBA of the type

Function MyFunc(Indx As Integer, k As Long, Rho As Range, A As Range) As Variant .... End Function

which is called as a user-defined function from within the Excel worksheet. When called with the last two arguments being a range (i.e. Result = MyFunc(1,98,A1:A2, B1:B2)) it works fine. However, when I try to directly use an array constant instead of a range (i.e. Result = MyFunc(1,98,{10,11}, {20,30}), it returns a #VALUE error.

I thought I could fix it by redefining the last two arguments as arrays of type double, but this didn't work either (i.e. Function MyFunc(Indx As Integer, k As Long, Rho() As Double, A() As Double) As Variant .... End Function ).

Does someone have a suggestion for a flexible solution, which would permit either calling method: by range, as well as by an array constant?

Thanks in advance!

hi,

need you guys help to solve a small problem of mine. have written a small vba code involving DO WHILE looping in excel. however i want to keep the column constant while keeping the rows variable. have attached the excel sheet and the code is in sheet 1. c if you guys can help me to get over with it.

I have VBA where I want to define a cell address that I use throughout the
macro, as a constant up at the top. This makes the code neater, but more
importantly makes it easier to make global changes without forgetting something,
and in conjunction with "Explicit" makes it harder to mistype something. A
routine programming practice.

What I have used up to now, and which works well, is something like:

CONST TargetCell As String = "Sheet3!A1"

The thing is that I'm now using this particular macro from various places. As a
result, I'd like to set the CONST based on the active sheet name when the macro
is fist called. After setting the CONST it would not change of course.

I've tried variations on:

CONST TargetCell As String = ActiveSheet.Name & "!A1"

VBA apparently does not like to base a constant value on something that may
change later and refuses to cooperate. After a lot of futzing around I ended up
defining it as a string variable rather than a constant and VBA allows that.

Just as a matter of programming practice though I'd rather use a constant. Is
there some way to persuade VBA to create a constant based on the value of a
variable at the moment the constant is created?

Thanks...

Bill

Hi,

In the Excel 2010 formula bar, if you type
=MATCH(,,
you then get a intellisense-style dropdown list of the options available for the third argument in the MATCH function, which are:

1-Less than
0 - Exact match
-1 Greater than

Does anyone know if/how it's possible to have this dropdown list appear for vba custom formulas that use a set of constants for the user to select from? It really would help if the end user can see the descriptions of the constants in the formula bar.

Thanks

I have a named range $B$11:$M$14 of which I used the below reference to create. The cells in this range are randomly coloured and the user drags them out to re group in colour groups in another part of the worksheet.
I need this named range to remain un changed in its position but I need to allow the user to freely remove groups of cells (by dragging) from the range area without upsetting/changing the originally set range.
To achieve this I used INDEX to anchor the range in its relevant position to remain constant. (otherwise dragging large groups of cells out of the range causes the named range to adjust.)

My problem now is -
That I need to be able to add and remove columns within the named range using a VBA sub() in a different workbook (Workbook2) as necessary, and also adjust the named range accordingly as I add the columns.

The only constant is the top left cell of the range $B$11 if this helps? But the width of the range may vary depending on the Workbook being used by Workbook2 at the time.
How do I do this?

Starting named range is below.

	VB:
	
=INDEX(Sheet1!$A:$Z, 11,2):INDEX(Sheet1!$A:$Z, 14,13) 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


I am trying to convert this macro Recoded code Completely into VBA mainly to reduce the length of the code and also primarily for the creating a dynamic range . But I keep getting an error .My main error is probably because I am mixing them both and not sure where to correct the code.
most of these VBA code have been pulled from the net
I need to know what is going wrong here.


	VB:
	
 
 ' Downloaded from www.contextures.com
Sub CreateNames() 
     ' written by Roger Govier, Technology4U
     
     
    Dim wb As Workbook, ws As Worksheet 
    Dim lrow As Long, lcol As Long, i As Long 
    Dim myName As String, Start As String 
     
     
     ' set the row number where headings are held as a constant
     ' change this to the row number required if not row 1
    Const Rowno = 1 
     
     
     ' set the Offset as the number of rows below Rowno, where the
     ' data begins
    Const ROffset = 1 
     
     
     ' set the starting column for the data, in this case 1
     ' change if the data does not start in column A
    Const Colno = 1 
     
     ' Set an Offset from the starting column, for the column number that
     ' will always have data entered, and will therefore be used in calculating lrow
     
    Const COffset = 0 ' in this case, the first column will always contain data.
     
     
    On Error Goto CreateNames_Error 
     
     
    Set wb = ActiveWorkbook 
    Set ws = ActiveSheet 
     
     
     ' count the number of columns used in the row designated to
     ' have the header names
     
    lcol = Cells(Rowno, Columns.Count).End(xlToLeft).Column 
    lrow = ws.Cells(Rows.Count, Colno).End(xlUp).Row 
    Start = Cells(Rowno, Colno).Address 
     
    wb.Names.Add Name:="lcol", RefersTo:="=COUNTA($" & Rowno & ":$" & Rowno & ")" 
    wb.Names.Add Name:="lrow", RefersToR1C1:="=COUNTA(C" & Colno + COffset & ")" 
    wb.Names.Add Name:="myData", RefersTo:= _ 
    "=" & Start & ":INDEX($1:$65536," & "lrow," & "Lcol)" 
     
     
    For i = Colno To lcol 
         ' if a column header contains spaces, replace the space with an underscore
         ' spaces are not allowed in range names.
        myName = Replace(Cells(Rowno, i).Value, " ", "_") 
        If myName = "" Then 
             ' if column header is blank, warn the user and stop the macro at that point
             ' names will only be created for those cells with text in them.
            MsgBox "Missing Name in column " & i & vbCrLf _ 
            & "Please Enter a Name and run macro again" 
            Exit Sub 
        End If 
        wb.Names.Add Name:=myName, RefersToR1C1:= _ 
        "=R" & Rowno + ROffset & "C" & i & ":INDEX(C" & i & ",lrow)" 
nexti: 
    Next i 
     
     
    On Error Goto 0 
    MsgBox "All dynamic Named ranges have been created" 
    Exit Sub 
     
     
    Exit Sub 
     
     
CreateNames_Error: 
     
     
    MsgBox "Error " & Err.Number & " (" & Err.Description & _ 
    ") in procedure CreateNames of Module Technology4U" 
     
     
     '
    Set myData = Range 
    Application.ScreenUpdating = False 
    myData.Select 
     
    .Columns("E:J").Select 
    .Selection.DELETE Shift:=xlToLeft 
    .Range("F7").Select 
    Columns("F:F").EntireColumn.AutoFit 
    Columns("A:A").ColumnWidth = 13.43 
    Columns("B:B").ColumnWidth = 13.29 
    Columns("A:A").ColumnWidth = 22.86 
    Columns("A:B").Select 
    Selection.DELETE Shift:=xlToLeft 
    Columns("C:C").Select 
    Selection.Cut 
    Columns("A:A").Select 
    Selection.Insert Shift:=xlToRight 
    Columns("C:C").Select 
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove 
    Range("C2").Select 
    ActiveCell.FormulaR1C1 = "=RC[-2]&""-""&RC[-1]" 
    Range("C2").Select 
    Selection.AutoFill Destination:=Range("C2:C304") 
    Range("C2:C304").Select 
    Range("C2").Select 
    Columns("C:C").ColumnWidth = 15.86 
    Range("C1").Select 
    ActiveCell.FormulaR1C1 = "StoreInvoice" 
    Range("C2").Select 
    ActiveWindow.ScrollRow = 2 
    ActiveWindow.ScrollRow = 7 
    ActiveWindow.ScrollRow = 11 
    ActiveWindow.ScrollRow = 18 
    ActiveWindow.ScrollRow = 24 
    ActiveWindow.ScrollRow = 39 
    ActiveWindow.ScrollRow = 59 
    ActiveWindow.ScrollRow = 93 
    ActiveWindow.ScrollRow = 121 
    ActiveWindow.ScrollRow = 155 
    ActiveWindow.ScrollRow = 187 
    ActiveWindow.ScrollRow = 207 
    ActiveWindow.ScrollRow = 214 
    ActiveWindow.ScrollRow = 223 
    ActiveWindow.ScrollRow = 228 
    ActiveWindow.ScrollRow = 236 
    ActiveWindow.ScrollRow = 240 
    ActiveWindow.ScrollRow = 243 
    ActiveWindow.ScrollRow = 246 
    ActiveWindow.ScrollRow = 250 
    ActiveWindow.ScrollRow = 251 
    ActiveWindow.ScrollRow = 253 
    ActiveWindow.ScrollRow = 255 
    ActiveWindow.ScrollRow = 256 
    ActiveWindow.ScrollRow = 257 
    ActiveWindow.ScrollRow = 260 
    ActiveWindow.ScrollRow = 261 
    ActiveWindow.ScrollRow = 262 
    ActiveWindow.ScrollRow = 264 
    ActiveWindow.ScrollRow = 265 
    ActiveWindow.ScrollRow = 266 
    ActiveWindow.ScrollRow = 268 
    ActiveWindow.ScrollRow = 273 
    ActiveWindow.ScrollRow = 277 
    ActiveWindow.ScrollRow = 281 
    ActiveWindow.ScrollRow = 283 
    ActiveWindow.ScrollRow = 274 
    ActiveWindow.ScrollRow = 265 
    ActiveWindow.ScrollRow = 257 
    ActiveWindow.ScrollRow = 253 
    ActiveWindow.ScrollRow = 244 
    ActiveWindow.ScrollRow = 238 
    ActiveWindow.ScrollRow = 227 
    ActiveWindow.ScrollRow = 219 
    ActiveWindow.ScrollRow = 211 
    ActiveWindow.ScrollRow = 206 
    ActiveWindow.ScrollRow = 200 
    ActiveWindow.ScrollRow = 197 
    ActiveWindow.ScrollRow = 191 
    ActiveWindow.ScrollRow = 188 
    ActiveWindow.ScrollRow = 184 
    ActiveWindow.ScrollRow = 177 
    ActiveWindow.ScrollRow = 172 
    ActiveWindow.ScrollRow = 167 
    ActiveWindow.ScrollRow = 162 
    ActiveWindow.ScrollRow = 155 
    ActiveWindow.ScrollRow = 151 
    ActiveWindow.ScrollRow = 146 
    ActiveWindow.ScrollRow = 141 
    ActiveWindow.ScrollRow = 135 
    ActiveWindow.ScrollRow = 131 
    ActiveWindow.ScrollRow = 124 
    ActiveWindow.ScrollRow = 120 
    ActiveWindow.ScrollRow = 114 
    ActiveWindow.ScrollRow = 111 
    ActiveWindow.ScrollRow = 106 
    ActiveWindow.ScrollRow = 103 
    ActiveWindow.ScrollRow = 99 
    ActiveWindow.ScrollRow = 97 
    ActiveWindow.ScrollRow = 93 
    ActiveWindow.ScrollRow = 90 
    ActiveWindow.ScrollRow = 88 
    ActiveWindow.ScrollRow = 85 
    ActiveWindow.ScrollRow = 83 
    ActiveWindow.ScrollRow = 82 
    ActiveWindow.ScrollRow = 80 
    ActiveWindow.ScrollRow = 78 
    ActiveWindow.ScrollRow = 77 
    ActiveWindow.ScrollRow = 75 
    ActiveWindow.ScrollRow = 73 
    ActiveWindow.ScrollRow = 70 
    ActiveWindow.ScrollRow = 68 
    ActiveWindow.ScrollRow = 63 
    ActiveWindow.ScrollRow = 60 
    ActiveWindow.ScrollRow = 56 
    ActiveWindow.ScrollRow = 53 
    ActiveWindow.ScrollRow = 50 
    ActiveWindow.ScrollRow = 46 
    ActiveWindow.ScrollRow = 44 
    ActiveWindow.ScrollRow = 40 
    ActiveWindow.ScrollRow = 39 
    ActiveWindow.ScrollRow = 36 
    ActiveWindow.ScrollRow = 33 
    ActiveWindow.ScrollRow = 29 
    ActiveWindow.ScrollRow = 27 
    ActiveWindow.ScrollRow = 24 
    ActiveWindow.ScrollRow = 22 
    ActiveWindow.ScrollRow = 21 
    ActiveWindow.ScrollRow = 19 
    ActiveWindow.ScrollRow = 17 
    ActiveWindow.ScrollRow = 15 
    ActiveWindow.ScrollRow = 13 
    ActiveWindow.ScrollRow = 12 
    ActiveWindow.ScrollRow = 10 
    ActiveWindow.ScrollRow = 8 
    ActiveWindow.ScrollRow = 7 
    ActiveWindow.ScrollRow = 5 
    ActiveWindow.ScrollRow = 3 
    ActiveWindow.ScrollRow = 2 
    ActiveWindow.ScrollRow = 1 
    Columns("L:L").Select 
    Selection.DELETE Shift:=xlToLeft 
    Columns("K:K").ColumnWidth = 16.71 
    Range("N7").Select 
    Application.ScreenUpdating = False 
End Sub 
Sub Del_rows_with_zero_in_column_of_activecell() 
     'Charles Chickering, programming, 2007-02-09
    Application.ScreenUpdating = False 
    Application.Calculation = xlCalculationManual 
    Const StartRow As Long = 1 'Row to Start looking at
    Dim StopRow As Long 
    Dim Col As Long 
    Col = ActiveCell.Column 
    StopRow = Cells(Rows.Count, Col).End(xlUp).Row 
    Dim cnt As Long 
    For cnt = StopRow To StartRow Step -1 
        If Not IsEmpty(Cells(cnt, Col)) Then 
            If IsNumeric(Cells(cnt, Col)) Then 
                If Cells(cnt, Col) = "" Then Rows(cnt).DELETE 
            End If 
        End If 
    Next cnt 
    Application.Calculation = xlCalculationAutomatic 
    Application.ScreenUpdating = True 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


I could use a little help on this.

I have a rather large spread sheet approx. 66K rows, and 57 columns. Column A-F are constants, and columns G-BE control the configuration (or help determine what the Part numbers in Column A are allowed).

I would like to take the information in Columns A-F and have one instance for each configuration type. I believe the method I was going through is not the best way due to the size of the spreadsheet and I have to believe there is a better way to go about it.

My thought process was to remove all the empty cells in each row, and then when I'm left with no spaces I could run through a For-Next statement and copy insert and delete. But As I'm working on a method to delete, the empty cells it is taking a rediculously long time, so either my coding to do this is way wrong and I'm in some sort of error loop, or it just isn't a good idea.

Since I'm relatively new to VBA, I'm hoping someone might be able to provide some guidance. I've attached a sample work book of my data and what I'm looking for.

Howdy, I currently am trying to tidy up a Macro that I wrote for a co-worker. Here is how it currently looks...


	VB:
	
"=VLOOKUP(RC1,'1 ADMIN Fleet Eq Data'!R2C1:R2500C10,3,FALSE)" 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Because of workers constantly changing sheet names and this name is used a lot I set the name to wsAdmin.

RC1 is supposed to refer to cell A2 in wsAdmin worksheet. There is about 2000 values that I want to match from the ActiveSheet to wsAdmin... and I did this using VLOOKUP. I wrote this code a while back when I wasn't very familiar with VBA. Now I want it to look like this.


	VB:
	
ActiveCell.Formula = "=VLOOKUP(A2,wsAdmin.Range((C2).End(xlToRight),(C2).End(xlDown),3,FALSE) 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
I understand that is very crappy looking, that is why I need help. It won't work. Also is there an easiar way to do this, I think Looping might take to long. Let me know what you think.

Thanks

Hi – I’m really hoping someone can help me with some code to display data in the format I’m looking for. I’m still learning about vba code and I’ve been stuck trying to find a solution, as the ranges in my data will change from week to week.

I have attached my data sample here. I have a range of values in column H that will change in my future data samples, so the code has to be based on the number of unique values in this dynamic range. The values in column A are also unique, they are listed vertically to correspond with each unique value in column H.

MY DESIRED GOAL: I am trying to have the vertical data in columns A, H-J output horizontally based on my dynamic range in row H. I used a formula to calculate the number of unique values in column H, but I still need to figure out how to make the formula dynamic (the formula is located in cell L1).

I created an example of what I want the data to look like in the end, you can see this to the right of column L. I have a grid where the unique values from column H are listed across the top starting in cell O1. Underneath that, there are two horizontal rows for each value in column A, one row is hard-coded titled “weekend” and the second row is hard-coded titled “weekday”. I used two different colors in attempt to better illustrate. The data from columns I-J should output horizontally in this grid, starting in cell O2 in my example. Once the first two rows of data have been populated in the grid, the code needs to know to move down to begin the next loop (i.e. move to O4-O5 to work on the data for the next unique value in column A, which starts in row 22 in my sample). This needs to continue until all rows (i.e. in my sample there are 1121 rows of data, but the number of rows will be dynamic in future data) have been outputted into the grid. The biggest problem I have is that this data sample will change from week to week, the only thing constant is that I know what type of data will be in each column, but the number of unique values in column H will always change.

Note: it doesn’t matter to me if the final data is outputted on the same tab like I have it now, or if it’s on a new tab.

CODE: I don’t have much code at this point since I’m not quite sure from what angle to come at this with, I just have a few very basic samples that I have come up with so far. Here is some static code for getting the values for O2-O3 based on the values in I2 and J2; this is just a model to work off of to build out the grid I need, again I need to figure out how to make it dynamic:


	VB:
	
If Range("I2") = "No" Then 
    Range("O2").Value = "No" 
End If 
If Range("I2") = "No" And Range("J2") = "Yes" Then 
    Range("O3").Value = "Yes" 
End If 
If Range("I2") = "No" And Range("J2") = "No" Then 
    Range("O3").Value = "No" 
End If 
 
 'if it's a weekend event
If Range("I2") = "Yes" Then 
    Range("O3").Value = "No" 
End If 
If Range("I2") = "Yes" And Range("J2") = "Yes" Then 
    Range("O2").Value = "Yes" 
End If 
If Range("I2") = "Yes" And Range("J2") = "No" Then 
    Range("O2").Value = "No" 
End If 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

I also have some code to transpose the number of unique values listed in column H to list them horizontally starting in O1, but my formula in L1 somehow needs to be modified to be dynamic.


	VB:
	
 
 
iNumEvents = Range("L1") + 1 
 
Range(Cells(2, 8), Cells(iNumEvents, 8)).Select 
Selection.Copy 
Range("O1").Select 
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _ 
False, Transpose:=True 
Range("O1").Select 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Any help would be truly appreciated. This is my first time posting so please forgive anything I may have overlooked.

Hello,

I am trying to use the "ThisWorkbook.Name" property for the workbook name in Vlookup

I previously used the following which worked -


	VB:
	
ActiveCell.FormulaR1C1 = _ 
"=VLOOKUP(C[-1],'[myworkbook.xls]Sheet1'!R1C2:R100C2,1,FALSE)" 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
I change the workbook name often so I figured instead of constantly changing the name in the vba code, why not use
ThisWorkbook.name...unfortunatley it does not work when I do the following...


	VB:
	
ActiveCell.FormulaR1C1 = _ 
"=VLOOKUP(C[-1],'[ThisWorkbook.Name]Sheet1'!R1C2:R100C2,1,FALSE)" 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

Any suggestions on this would be appreciated.

Thank you!

Hello Everybody,
I have a situation, I need to declare a variable array, taking the source from a array in a sheet in the workbook, because the user needs to increase or decrease the list of values, ok.
Because of this, i need to indicate in the macro to take la total of rows but is unknown coz the user control it.


	VB:
	
 Range 
Set exRNG = Range("excepciones") 
 
Const eQty = Range("qtyExcep").Value 'here is my headheache
 
Const exCount = eQty 
 
Dim NoList(1 To exCount) As String 
 
For Each M In exRNG 'beging to store the data into the variable array
    counter = counter + 1 
    NoList(counter) = M.Value 
Next M 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
VBA says to me "Constant expression required"... but i dont know how to do this...
Please guys... send me your ideas! thks in advance!!


No luck finding an answer? You could always try Google.