How can I determine the row address of an existing xlPageBreakFull, using VBA.

I've searched the VBA help files, the database here at OZgrid and can't find the answer. I know the code to add pagebreaks, remove them, etc. but darned if I can work out the code to determine the location of an existing page break.

Someone, help, PLEASE

Thanks

Ted................

I've searched the VBA help files, the database here at OZgrid and can't find the answer. I know the code to add pagebreaks, remove them, etc. but darned if I can work out the code to determine the location of an existing page break.

Someone, help, PLEASE

Thanks

Ted................

- Using VBA to to hide a row based on multiple values
- Determine pagebreak using VBA
- Changing Conditional Format Relative to Absolute Addressing Using VBA
- How to programmatically determine which rows are outlined using Excel VBA code
- How can i insert an e-mail address to a worksheet using VBA
- Separating an address string into separate parts using vba
- non linear equations solver using VBA
- HELP - How do I determine my computers IP address through VBA in Excel
- How to determine using vba if any data is present in the col
- How can I use vba to determine the name of an interior color?
- Help with using vba to determine file associations
- Using VBA to determine different ranges of data set
- Using vba to determine the positions of a character repeated twice in string
- Obtain mac address using vba code in windows 7
- Find last column in print range using VBA when text overflows into adjacent cells
- Use VBA to open a file with a date in the filename
- Creating Multiple Print Areas automatically using VBA
- Use VBA to implement min/max and pass/fail formulas
- VBA: when to use VBA
- Transposing using vba
- Relative Reference using vba Range
- Custom Menu using VBA
- Converting Cells.Address to relative address in VBA for excel
- Running programs using VBA

Right now I've got some conditional formatting set that simply changes the font color to white for any value that is '0'. This is to remove the extraneous numbers in a quick and easy way since we don't care about anyone who has 0 availability for a given month or week. We also don't care about anyone who has 0 availability for the entire year, which is why I would like to completely hide their row if their entire year is 0.

My understanding of filtering within a pivot table is that you can only associate a value filter with one column. Therefore if I tried to use a value filter to not show 0's in the Sum of January column, the filter would remove Ned and Patrick, even though I only want to remove Patrick.

This question is similar to mine, but instead of one cell I want it to apply to the range of cells. Maybe something like IF SUM of January throught April = 0 , then hide the row? The main problem I'm having is translating that code and making it work for me. Let me know if you can help!

------

Condition 1

Formula Is =OR(N7=O7,N7=P7)

Condition 2

Formula Is =OR(AND(N7=6,O7=0),AND(N7=0,O7=6))

Condition 3

Formula Is =OR(AND(N7=6,P7=0),AND(N7=0,P7=6))

------

However each cell has different relative addresses that they refer to (each on their own line).

The original reason that I made the addressing relative is because I wanted to copy these formats to many other places where they had to all refer to the cells on their own line, which I have done.

But now I am trying to write code to have Excel test to see if one of these conditional formats is actually active or not, and this code evidently needs the conditional formats to have absolute addressing, otherwise the code will not work (I've tested this and it seems to be true).

In order not to have to go through all of the conditional formats and change them manually, I would like to have a VBA function that can do this automatically.

In short I would like to have a something similar to Dave Hawley's 'MakeAbsoluteorRelative' function (http://www.ozgrid.com/forum/showthre...979#post227979) that will make all of the addressing whatever I want it to be, but inside of all of the conditional formatting only (not in the cells themselves as Dave's excellent code does)?

I just want to use a macro to change the addressing format of the conditional formatting in ALL of the cells that I highlight, from Relative to Absolute or from Absolute to Relative (or any of the two combinations of relative/absolute or absolute/relative), just as Dave's macro does.

I know how to 'manually' change from relative to absolute, etc, and I know how to copy so that the references are correct. What I want to do, I could do manually but it would take a very long time and will not help me in the future. Any help would be greatly appreciated.

VBA code) which rows and/or columns are outlined on an

Excel spreadsheet?

I have spreadsheets with three different groups of columns

outlined (one level of outline demotion each - no

overlap). The specific columns that are outlined change

based on the data. I would like to write VBA code to 1)

programmatically determine the groups of outlined columns,

and then, 2) reduce the number of outlined columns in one

of the groups (the third one).

Example using R1C1 reference notation: Outlined

columns: 1, 5-12, and 19-40

My goal is to reduce the third outline group by the first

three columns of the group. In this case group 19-40

would have columns 19-21 promoted by using code like

Range(Cells(1, 19), Cells(1, 21)).Select

Selection.Columns.Ungroup

It is easy to promote the columns manually, but it is a

pain to do over and over again at the end of an otherwise

automated process (VBA code) that is formatting the

spreadsheet.

For the curious - why do I want to promote three columns

programmatically? VBA code is used to extensively format

an Excel file including adding lookup table data as new

worksheets, adding formulas that use the VLOOKUP function,

a macro button and code "behind" the button to provide

sort options, borders, outlining to hide some cost history

columns and future projections columns so a window of data

around the current month data is displayed and the report

is constrained to one print page wide on 11" wide by 17"

high tabloid size paper at about 64% reduction, etc. The

data includes hours and dollars totals for labor,

material, travel, etc. A "values only" summary of the

totals rows is created as a separate (11" wide by 8-1/2"

high when printed) "Summary" sheet. Additional

calculations are added to the summary sheet

programmatically. Some columns at the right side of the

data are not needed on the summary sheet. These columns

are deleted. This leaves the summary sheet "viewable

print data" not as wide as the area available. Removing

three columns from the hidden outline of the future months

fills the page and shows more data.

The kind of outlining I am referring to has to do with how

to show or hide detail data (i.e., rows and/or columns) in

an outline. If you enter this phrase, "Show or hide

detail data in an outline", in the Help Answer Wizard you

will see how outlining is done manually.

I want to programmatically determine which columns

are "outlined" (i.e., can be shown by a click on a "+"

button in the margin of the spreadsheet outside the grid

area or hidden easily by a click on a "-" button in the

margin of the spreadsheet outside the grid area. I assume

there is a collection where this data is stored (something

like columns.outline or some such).

Thanks for any help.

recipients, getting their SMTP address from a table in the workbook. Can

this be done using VBA?

I have an address string that I need to split up into three cells using vba with the mid, right, and left functions. The problem that I'm having is that I can't seem to formulate the functions in which they can handle different forms of the address.

For example,

New York, NY 23658 should return New York in one cell, NY in the next, and 23658 in the next.

New York, NY should return New York in one cell, NY in the next, and nothing in the next because there isn't a zip code.

The code I have for the first example is:

Sheet1.Cells(i, n + UBound(Selection.Value, 2) + 1) = Trim(Left(CityStateZip,Find(" ",CityStateZip) Len(CityStateZip) - 10)) Sheet1.Cells(i, n + UBound(Selection.Value, 2) + 2) = Trim(Mid(CityStateZip, Len(CityStateZip) - 8, 4)) Sheet1.Cells(i, n + UBound(Selection.Value, 2) + 3) = Trim(Right(CityStateZip, 5))This code works. I just need to amend it for the second example.

Thanks in advance,

Can anyone suggest how I augment the code to overcome this problem (division by zero pivot term) or has anyone else a better code for non linear equations solver. CODE BELOW

''''''''''''''''''''CODE'''''''''''''''''''''''''''''''

Option Explicit

Option Base 1

Const ConvergenceTolerance = 0.00000001

Const IncermentNumericalDifferentiation = 0.000000001

Const Iterations = 50

Const expon = 2.718281828

Function SimultEqNL(equations, Variables, constants)

' Newton iteration method to find roots of nonlinear simultaneous equations

' Example:

' w^3 + 2w^2 + 3w + 4 = +12.828

' w.x + x.y + y.z = -3.919

' w^2 + 2w.x + x^2 = +1

' w + x + y - z = -3.663

'

' WHERE: constants = [12.828,-3.919,1,-3.663];

'On Error Resume Next

Dim i As Integer, j As Integer, k As Integer, N As Integer

Dim Nlterations As Integer

Dim R As Integer, C As Integer

Dim VarAddr() As String, FormulaString() As String

Dim con() As Double, A() As Double, B() As Double

Dim V() As Double

Dim Y1 As Double, Y2 As Double

Dim tolerance As Double, incr As Double

N = equations.Rows.count

k = Variables.Rows.count

If k = 1 Then k = Variables.Columns.count

If k <> N Then SimultEqNL = CVErr(xlErrRef): Exit Function

' Use the CVErr function to create user-defined errors in user-created procedures.

' For example, if you create a function that accepts several arguments and normally returns a string,

' you can have your function evaluate the input arguments to ensure they are within acceptable range.

' If they are not, it is likely your function will not return what you expect.

' In this event, CVErr allows you to return an error number that tells you what action to take.

ReDim VarAddr(N), FormulaString(N), V(N), con(N)

ReDim A(N, N + 1), B(N, N + 1)

tolerance = ConvergenceTolerance 'Convergence criterion.

incr = IncermentNumericalDifferentiation 'Increment for numerical differentiation.

Nlterations = Iterations

For i = 1 To N

VarAddr(i) = Variables(i).Address

' i.e. VarAddr(1) = $A$11

Next i

' Initial values

For i = 1 To N

con(i) = constants(i).Value

' Put the initial guesses into vector V()

V(i) = Variables(i).Value: If V(i) = 0 Then V(i) = 1

Next i

For j = 1 To Nlterations

' Create N x N matrix of partial derivatives.

For R = 1 To N ' n = equations.Rows.count

For C = 1 To N

' Formulastring is formula in which all but one variable in each equation is replaced by current values.

FormulaString(R) = Application.ConvertFormula(equations(R).Formula, xlA1, xlA1, xlAbsolute)

' xlA1 = Use xlA1 to return an A1-style reference. xlR1C1 = Use xlR1C1 to return an R1C1-style reference

' xlAbsolute = Convert to absolute row and column style

' ConvertFormula method used to convert cell references from A1 reference style to R1C1 reference style

For i = 1 To N

' Debug.Print FormulaString(R)

If i <> C Then FormulaString(R) = Application.Substitute(FormulaString(R), VarAddr(i), V(i))

' Substitutes new_text for old_text in a text string.

' FormulaString(R) = the reference to a cell containing text for which you want to substitute characters.

' Replace the address reference with the actual variable value i.e. $B$5^3 + 2*$B$5^2 + 3*$B$5 + 4-$R$5+3-1^3

' i = C means its on the diagonal of the matrix

Next i

' V() = vector of current variable values

If IsError(Evaluate(Application.Substitute(FormulaString(R), VarAddr(C), V(C) * (1 + incr)))) Then MsgBox "ERROR IS FORMAULA EVALUATION"

If IsError(Evaluate(Application.Substitute(FormulaString(R), VarAddr(C), V(C) * (1 - incr)))) Then MsgBox "ERROR IS FORMAULA EVALUATION"

' Y2 = Evaluate(Application.Substitute(FormulaString(R), VarAddr(C), (V(C) + incr)))

' ' value of the equation at the current variable figure i.e. instead of evaluating the equation at VarAddr(C) evaluate at V(C)*(1+incr)

' Y1 = Evaluate(Application.Substitute(FormulaString(R), VarAddr(C), (V(C) - incr)))

' A(R, C) = (Y2 - Y1) / (2 * incr)

Y2 = Evaluate(Application.Substitute(FormulaString(R), VarAddr(C), V(C) * (1 + incr)))

' value of the equation at the current variable figure i.e. instead of evaluating the equation at VarAddr(C) evaluate at V(C)*(1+incr)

Y1 = Evaluate(Application.Substitute(FormulaString(R), VarAddr(C), V(C) * (1 - incr)))

A(R, C) = (Y2 - Y1) / (2 * incr * V(C))

'

' Derivatives i.e Taylor Series approx (numerical differentiation) central difference:

' y' = (y,i+1 - y,i-1) / 2h where y' = derivative of y, h = step size

' F(x+h) = F(x) + hF'(x) thus F'(x) = [ F(x+h)-F(x) ] / h

Next C

Next R

'Augment matrix of derivatives with vector of constants.

For R = 1 To N

FormulaString(R) = Application.ConvertFormula(equations(R).Formula, xlA1, xlA1, xlAbsolute)

For C = 1 To N

FormulaString(R) = Application.Substitute(FormulaString(R), VarAddr(C), V(C))

Next C

If IsError(Evaluate(FormulaString(R))) Then MsgBox "ERROR IN FORMULA FO Augment matrix of derivatives"

A(R, N + 1) = con(R) - Evaluate(FormulaString(R))

Next R

For i = 1 To N

If Abs((A(i, N + 1)) / V(i)) > tolerance Then GoTo Refine

Next i

SimultEqNL = Application.Transpose(V)

Exit Function

Refine: Call GaussJordan3(N, A, B)

'Update V values

For i = 1 To N

V(i) = V(i) + A(i, N + 1)

Next i

'Debug.Print j, "", V(1), V(2), V(3), V(4) ', V(5), V(6), V(7), V(8), V(9)

Next j

' Exit here if no convergence after a specified number of iteration

SimultEqNL = CVErr(xlErrNA)

End Function

'. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

Sub GaussJordan3(N, AugMatrix, TempMatrix)

Dim i As Integer, j As Integer, k As Integer, L As Integer, LL As Integer, P As Integer, M As Integer, MM As Integer, MMM As Integer, MMMM As Integer

Dim pivot As Double, temp As Double, arr1() As Variant, arr2() As Variant, x As Integer, y As Integer

Dim determine As Integer, xx As Integer, yy As Integer, cntr As Integer, fudgefactor As Double

determine = 0

x = UBound(AugMatrix, 1): xx = UBound(TempMatrix, 1) ' =Number of ROWS

y = UBound(AugMatrix, 2): yy = UBound(TempMatrix, 2) ' =Number of COLUMNS' =Number of COLUMNS

' Debug.Print x, y, xx, yy

ReDim arr1(1 To 1, 1 To y): ReDim arr2(1 To 1, 1 To y)

For k = 1 To N

' Locate largest matrix element, use as pivot.

pivot = AugMatrix(k, k): P = k

For L = k + 1 To N ' loop each row

If Abs(AugMatrix(L, k)) < Abs(pivot) Then GoTo EndOfLoop

pivot = AugMatrix(L, k)

P = L

EndOfLoop: Next L ' next row

'Debug.Print pivot

' Swap rows

For j = 1 To N + 1

temp = AugMatrix(k, j)

AugMatrix(k, j) = AugMatrix(P, j)

AugMatrix(P, j) = temp

Next j

' Normalize pivot row

For j = 1 To (N + 1)

'If pivot = 0 Then MsgBox "PIVOT is ZERO"

If pivot = 0 Then Debug.Print "****",

TempMatrix(k, j) = AugMatrix(k, j) / pivot

Next j

' Do the Gauss elimination.

For i = 1 To N

If i = k Then GoTo EndOfLoop2

For j = 1 To N + 1

TempMatrix(i, j) = AugMatrix(i, j) - AugMatrix(i, k) * TempMatrix(k, j)

Next j

EndOfLoop2: Next i

For i = 1 To N

For j = 1 To N + 1

AugMatrix(i, j) = TempMatrix(i, j)

Next j

Next i

Debug.Print k, pivot

Next k

End Sub

Does anyone knows how can i do this???

I have a situation where i have headers in a file but just because there is a header does not mean there is any corresponding data in that col. I was keying off if just the header was present then i need to do "something" but now i need to key off two things:

1) Header present

2) If there is anydata in the col when a header is present.

Question: How to use vba to determine (quickly) if there is any data in a given column.

Thanks,

Trebormac

?

e.g. AcdSee, Microsoft Picture Viewer etc

Regards & TIA

For example: Row 1 to Row 56 belongs to Company A. And Row 57 to Row XX belongs to Company B and so on until 100 companies. For each company it has different range of data and we could not determine the number of the companies and the name as well. How could we use VBA to read and distinguish the name of the company in order to perform calculation for each company?

You can use the InStr function to determine the position of a character in a string. However, what if you want to determine the position of the character, which is used twice in the string? For example:

abc def & abc xyz & abc a

I would want to know the position of the first & and also the position of the second & using VBA.

Thanks for response

I am using the code below to obtain my pc's mac address. It is good at windows xp. However it is useless in windows 7. Is there anybody who can help me. Thank you all.

VB:Dim objVMI As Object Dim vAdptr As Variant Dim objAdptr As Object Dim adptrCnt As Long Dim adres As String adres = "" Set objVMI = GetObject("winmgmts:" & "." & "rootcimv2") Set vAdptr = objVMI.ExecQuery("SELECT * FROM Win32_NetworkAdapterConfiguration WHERE IPEnabled = True") For Each objAdptr In vAdptr If Not IsNull(objAdptr.MACAddress) And IsArray(objAdptr.IPAddress) Then For adptrCnt = 0 To UBound(objAdptr.IPAddress) If Not objAdptr.IPAddress(adptrCnt) = "0.0.0.0" Then GetNetworkConnectionMACAddress = objAdptr.MACAddress Exit For End If Next adptrCnt 'MsgBox "Your MAC Address is: " & GetNetworkConnectionMACAddress adres = GetNetworkConnectionMACAddress End If Next GetMACAddress = adres End FunctionIf you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

I need help creating a VBA code that finds the last column within the print area viewed in Page Break View. The tricky part is that one of the cells in the last used column has text that overflows into 10 adjacent cells. This can be seen in the picture below (and also in the attached Excel file). The critical overflowing cell is highlighted in yellow.

book3.jpg

For my purposes, I cannot use the built-in column autofit feature. Also, for this worksheet, I have the page setup to print to 1 page wide by 1 page tall in landscape orientation.

Using VBA code, how can I determine that Column S needs to be the right boundary of my print area?

Any help is greatly appreciated!

Book3.xls

I have a folder (C:userdatafiles) with a list of files such as:

userdata-01-01-2012.xls

userdata-01-02-2012.xls

userdata-01-03-2012.xls

userdata-01-04-2012.xls

How can I use vba to to open the file with the latest date? I cant use "last modified date" as the files could possibly be opened and changed at later dates. I need to be able to determine the latest date based upon the filename.

Many thanks

I wish to make all these tables individual print areas i.e end up with 365 print areas.

I wish to do this automatically using VBA code.

My only attempt to was use the following code on each table using a loop.

VB:I used Step Into to view the operation of the code one line at a time.PrintArea() Application.ScreenUpdating = False Worksheets("Sheet1").Activate For t = 1 To 13141 Step 36 Range("A1")(t).Activate ActiveSheet.PageSetup.PrintArea = ActiveCell.CurrentRegion.Address Next t Application.ScreenUpdating = True End SubIf you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

Unfortunately as soon as the second table became a print area the first table print area was deselected

As soon as the third table became a print area the second table print area was deselected and so on.

The result of running the code was simply to make the last table a print area.

Any suggestions please ?

I am quite willing to upload a copy of the file if this at all possible.

Regards

Flynne

I'm new to VBA programming. Hope someone can provide some tips on how to implement cell formulas using VBA codes.

I have trouble writting codes for column H and column J:

Basically, Col H is the difference of Max and Min in Col D,E,F,G,H.

Col J determines pass/fail status by comparing with specifications.

Worksheet is attached.

Thanks in advance.

I am a little confused as to which way to go. Does using many formula's slow a multi sheet workbook down? or is it a safer option, in regard to not having your app tampered with by curious users, to use code?

Thanks

This is a great site and I have learnt alot about VBA, by reviewing this site, however I am probably asking a really silly question but I am stuck in this nifty spreadsheet with one simple thing. What I would like to do is transpose say column A to start at say address B1 and continue through to say z1. using vba, without having to address each cell.

Ie.

column A column B column C column D

A s1 s2 s3

S1

S2

S3

etc.

Thanks in advance

nounei

I'm creating a formula for a cell using the range.address method in vba. I then use vba to copy this formula across many other cells. The only problem is that the range.address method returns a non-relative reference (ie "$A$1).

Sample code:

Code:

Is there someway to get rid of the "$" easily when returning .address?

Thanks,

Matt

I'm using VBA in excel and want to do the following as I would have done in a worksheet:

I have simplified my example tremendously, but I think this is adequate.

suppose cell A1 has the number 2

suppose the cell B1 has the number 3

in cell A2 in excel I would type "=sum(A1,B1)"

in vba I want to do the same, BUT I don't know the actual cell address and I want the ability to copy to other cells using relative addressing.

So far I am able to code:

variable_x="=" & Cells(ProductLineRow(1), r1USD).Address & "+" & Cells(ProductLineRow(1), r2USD).Address

(The range(cells) just hasn't been working for me. Not sure why.)

So my equation is great, except the result is not relative. It gives me back =$A$1+$B$1 in my cell. I want to see =A1+B1.

I tried doing .address.indirect but vba doesn't like that either. Not sure of correct syntax.

Any thoughts how to do this?

Thanks.

I am trying to integrate the ability to launch a program with command line arguments by clicking on a cell. More specifically, I'm trying to launch vncviewer, and point it to an IP address.

the command line would look like:

c:program filesultravncvncviewer.exe 192.168.XXX.XXX

The only text the cells will have in them is the IP address to the machine with which I would like to initiate a connection, and I'm hoping theres a way to automatically pull that information from the cell and run it in the command. Is there a way to do this? Better yet, is there a relatively simple way to do this?

Thanks,

Tyler