Solved: extracting info from cells

[vassili]

how can i extract useful info that's been typed into cells? i've got this form that varies in page numbers depending on the project. i need to add a column of extra cells to every page except the last. so on a 4 page project, i only need to add 3 columns to the first 3 pages.

to make things worse, all the pages are in one excel sheet and the 1st page column height is shorter than the following pages. (columns are same height thereafter)

i need to write code to automate the adding of these columns, but i can't figure out how to check the page number.

on the first page in CELL BD4 it does list "current page" of "total page"...so i think i can loop it based on the last number? but how do i go about extracting x out of "page 1 OF x" ???

there's always a 1st page so i can just create it from the bat, but what sort of loop should i use to add the green columns?

i've included the form so you guys can have a look. the highlighted parts are the columns i have to add. the yellow column is diff. height than the green ones.




thanks for any help you guys can give me.

[Oorang]

Name the range for each pages data then just refer to the name. You can name a range by highlighting it, then go to the "insert" menu and choose the "name" submenu then select "define". You can then refer to the range like so:
[VBA]Sub Example()
ActiveSheet.Range("Test").Cells(2, 3).Value = "My Test Value"
MsgBox ActiveSheet.Range("Test").Cells(2, 3).Value
End Sub[/VBA]

[vassili]

Name the range for each pages data then just refer to the name. You can name a range by highlighting it, then go to the "insert" menu and choose the "name" submenu then select "define". You can then refer to the range like so:
[vba]Sub Example()
ActiveSheet.Range("Test").Cells(2, 3).Value = "My Test Value"
MsgBox ActiveSheet.Range("Test").Cells(2, 3).Value
End Sub[/vba]

can you elaborate? i'm kind of confused with your explanation. the form varies in pages (all on one sheet) depending on project. i need to first check how many pages, then apply those extra columns...

so the specific form i posted was only 4 pages with 3 columns needing to be added, what happens when the next form comes in (under a diff. file name obviously, so how will the ranges i defined in the previous workbook work with the new one?) with 8 pages with 7 columns needed to be added? how can i refer to those extra 4 columns?

i may have implemented your sample code wrong, but all i get for an output was a msgbox...i'mno really understandin how it's supposed to work...

[austenr]

Why would you combine all of those columns like that? If you plan on manipulating the data in those cells you are in for a rough ride. Just my 2 cents worth.

[vassili]

Why would you combine all of those columns like that? If you plan on manipulating the data in those cells you are in for a rough ride. Just my 2 cents worth.

no, the data in those new columns will be a specific fomula to auto calculate totals from a bunch of cells within the form...no need after that to mainpulate further.

[rbrhodes]

Hi vasilli,

Do you create these forms or do they come to you already built with various numbers of pages?

Cheers,

dr

[rbrhodes]

Well anyways,

I thought it an interesting problem, so...

One of these will create Cols Based on "page 1 of X" although it's up to you to get it to work on whatever Book it needs to work on...

The other will create the number of pages requested from a hidden sheet named "Template" and build the Columns required for that number of sheets. The Clear button Clears and kills everything except 'Page 1' as you said there would always be a Page 1.

I commented everything so you could learn how to do it or at least adapt it...

Cheers,

dr

[vassili]

Well anyways,

I thought it an interesting problem, so...

....One of these will create Cols Based on "page 1 of X" although it's up to you to get it to work on whatever Book it needs to work on...

I commented everything so you could learn how to do it or at least adapt it...

Cheers,

dr

wow man, that is fantastic! especially form2 it looks like, where the code reads 1 of x. thank you so much. the inStr function is what i was lookin for.

though i do have a question. i can't get it to run. lol. i keep getting the noNumber error. i'm a little lost on how it's supposed to work.

i will be getting these forms (with varying pages) already filled out and completed fully. so the page numbers will already be there along with all the required fields. as i understand it, when i press the button or execute the code it will parse the string from BD4 and fiugr out the position of "f" and then subtract from the length to isolate the total page number?

i tried this code in the original form and on 2 other forms that have been comleted. i keep getting the noNumber error. could it be my 2002 excel is incompatible?

also, once the extra clumns are filled, how can i add formulas to them?

it will be basically multiplying the # in the adjacent cell going down column BD with a static quantity # from another sheet.

if u could pls just elaborate on how to get the code in form2 to work that would b great. and maybe a hint on how to apply formulas to the new cells. thanks again.

[vassili]

Hi vasilli,

Do you create these forms or do they come to you already built with various numbers of pages?

Cheers,

dr

they come to me already filled out completely, so there will already be page numbers keyed in by whoever does the form. thanks man.

[rbrhodes]

Hi v,

Here's the code with the formula input (see comments in sub). The formula right now in co, BN multiplies the Col BN value by 10. Change to suit.

Also I commented out the On Error line so we can trace the Error. Open the VBE, <Alt + F11> or Tools/ Macro/ Macros and put the cursor anywhere in the Sub. Press F8 to step through the sub and let me know which line errors out.

OR send me an example or two of the forms in which it is not working and I'll look at them for you.

Cheers,

dr

[vassili]

Hi v,

Here's the code with the formula input (see comments in sub). The formula right now in co, BN multiplies the Col BN value by 10. Change to suit.

Also I commented out the On Error line so we can trace the Error. Open the VBE, <Alt + F11> or Tools/ Macro/ Macros and put the cursor anywhere in the Sub. Press F8 to step through the sub and let me know which line errors out.

OR send me an example or two of the forms in which it is not working and I'll look at them for you.

Cheers,

dr

oops, did you forget to upload the xls?

[rbrhodes]

yeah did it again....

Option Explicit
Sub AddColumns()
Dim i As Long
Dim j As Long
Dim r As Long
Dim pg As Long
Dim totalPages As Long
    'speed
    Application.ScreenUpdating = False
 
    'page not found
'commented out to find error
    'On Error GoTo noNumber
 
    'find "f" in "page # of #"
    pg = InStr(Range("BD4"), "f")
 
    'get number from string
    totalPages = Right(Range("BD4"), Len(Range("BD4")) - pg)
 
    Select Case totalPages
        Case Is < 3
            'just do page 1
        Case Is >= 3
            'init r
            r = 5
            'do page 2 to pages -1 (not last page)
            For j = 2 To totalPages - 1
                'do page X. then page 1
                r = Range("BD" & r & ":BD65536").Find("page").Row + 3
                Range("BN" & r) = "Total Units"
                Range("BN" & r).WrapText = True
                For i = r To r + 46
                    Range("BN" & i).Select
                    With Selection
                        .Borders(xlDiagonalDown).LineStyle = xlNone
                        .Borders(xlDiagonalUp).LineStyle = xlNone
                    End With
                    With Selection.Borders(xlEdgeLeft)
                        .LineStyle = xlContinuous
                        .Weight = xlMedium
                        .ColorIndex = xlAutomatic
                    End With
                    With Selection.Borders(xlEdgeTop)
                        .LineStyle = xlContinuous
                        .Weight = xlMedium
                        .ColorIndex = xlAutomatic
                    End With
                    With Selection.Borders(xlEdgeBottom)
                        .LineStyle = xlContinuous
                        .Weight = xlMedium
                        .ColorIndex = xlAutomatic
                    End With
                    With Selection.Borders(xlEdgeRight)
                        .LineStyle = xlContinuous
                        .Weight = xlMedium
                        .ColorIndex = xlAutomatic
                    End With
            'CHANGE ME
                'this will put formula in cell BN(row number). The
                'formula here is =BM(row number) * 10. Change the
                'part:  "* 10" to whatever you need, keeping the quotes
                '
                'Also change at end of sub for page 1
                    Selection.Formula = "=BM" & i & "*10"
                Next i
            Next j
        Case Else
            'didn't get number!
            GoTo noNumber
    End Select
    'do page 1
    Range("BN7") = "Total Units"
    For i = 7 To 47
        Range("BN" & i).Select
        Range("BN" & i).WrapText = True
        With Selection
            .Borders(xlDiagonalDown).LineStyle = xlNone
            .Borders(xlDiagonalUp).LineStyle = xlNone
        End With
        With Selection.Borders(xlEdgeLeft)
            .LineStyle = xlContinuous
            .Weight = xlMedium
            .ColorIndex = xlAutomatic
        End With
        With Selection.Borders(xlEdgeTop)
            .LineStyle = xlContinuous
            .Weight = xlMedium
            .ColorIndex = xlAutomatic
        End With
        With Selection.Borders(xlEdgeBottom)
            .LineStyle = xlContinuous
            .Weight = xlMedium
            .ColorIndex = xlAutomatic
        End With
        With Selection.Borders(xlEdgeRight)
            .LineStyle = xlContinuous
            .Weight = xlMedium
            .ColorIndex = xlAutomatic
        End With
'CHANGE ME
    'formula for page 1
        Selection.Formula = "=BM" & i & "*10"
    Next i
    Exit Sub
 
    'reset
    Application.ScreenUpdating = True
 
noNumber:
    MsgBox ("Didn't get page number")
    'reset
    Application.ScreenUpdating = True
End Sub

[vassili]

SOLVED!

using rbrhodes' code, change the init value of r to 70 and voila, everything works.

also, for the formula part at the end, there needs to be a conditional statement so as to not overwrite the "Total Units" title row with a formula.

[VBA]If i > r Then
Selection.Formula = "=BD" & i & "*ITECH!$I$5"
Selection.HorizontalAlignment = xlCenter
Selection.VerticalAlignment = xlBottom
End If[/VBA]

also you need to increase r with

[VBA]r = r + 54[/VBA]

in the for loop after

[VBA]Next i[/VBA]

thanks guys! RBRHODS, you da man! if you're ever in toronto, i'll buy you a beer!

[rbrhodes]

Hi Will,

Glad you got it to work for you!

I'm in Nanaimo which is directly across the water from Vancouver, BC. on Vancouver Island. Haven't been to Toronto for a while but you never know!

Cheers,

dr