Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Rhinorhino on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Formula to word file

Status
Not open for further replies.
RamziSaab

RamziSaab

MIS
Joined
May 7, 2003
Messages
522
Location
GB
Just wondering if someone could tell me how difficult it would to write a VBA program that would:

Go down each cell in a colum if there is a formula write the formula into word not with cells references or values but with the text in column A:

e.g.
COLUMNS
A B
Apple 1
Orange 2
Total =B1+B2


so i would want word to write Cell B3: Total = Apple + Orange

tahnks
 
Sort by date Sort by votes
shouldn't be that tricky

Obviously, you will have an exterior loop to go down the cells in the column

You can test to see if the cell has a formula using the
.HASFORMULA property of the range so
Code: 
For each c in Range("A1:A1000")
  if c.hasformula then
    'do stuff here
  end if
next

To get the formula you can use

cellForm = c.formula

Now this is the tricky(ish) bit (depending on your formulae)

If all the formulae are absolute, then you can loop through the cellForm variable and check for "$". Where that appears, you know there is a cell ref so
Code: 
NewForm = ""
For i = 1 to len(cellForm)
 if mid(cellForm,i,1) = "$" then
    Cell_Ref = mid(cellForm,i,4)
    CellVal = Range(Cell_Ref).offset(0,-1).value
    NewForm = NewForm & CellVal
    i = i + 3 ' skip the rest of the cell ref
 else
    NewForm = NewForm & mid(cellForm,i,1)
 end if
next i

'Call Word and use:
"Cell " & c.address & ":" & c.offset(0,-1).value & NewForm

As a test, I created a layout as per your example with Total in A4 and =$B$2+$B$3 in B4

Select cells B1:B50 and ran the following - seems to work ok
Code: 
Sub test()
For Each c In Selection
  If c.HasFormula Then
    NewForm = ""
    cellForm = c.Formula
For i = 1 To Len(cellForm)
 If Mid(cellForm, i, 1) = "$" Then
    Cell_Ref = Mid(cellForm, i, 4)
    CellVal = Range(Cell_Ref).Offset(0, -1).Value
    NewForm = NewForm & CellVal
    i = i + 3 ' skip the rest of the cell ref
 Else
    NewForm = NewForm & Mid(cellForm, i, 1)
 End If
Next i

'Call Word and use put there instead but for testing purposes use debug.print
Debug.Print "Cell " & c.Address & ":" & c.Offset(0, -1).Value & NewForm

  End If
Next
End Sub

Rgds, Geoff

Never test the depth of water with both feet

Help us to help you by reading FAQ222-2244 before you ask a question
 
Upvote 0 Downvote
Geoff thanks

its starting to take form, could you tell me:

1.how to make an if statement that looks to see if the value in the string is a letter

2.how to make and if statement with or

i.e. If Mid(cellform, i, 1) = "=" Or "+" etc.

i think that is it for now... :)

thaks again
 
Upvote 0 Downvote
sorry small addition

if it find the letter G how do i tell it to offset by 6 while if its H by 7 etc. without writing many lines
 
Upvote 0 Downvote
Ramzi,
this should help for point 1:
Code: 
myStr = "ABC123ABC"
For i = 1 To 9
    [b]Select Case Asc(Mid(myStr, i, 1))[/b]
        Case 48 To 57
            Debug.Print "Number"
        Case Else
            Debug.Print "Letter"
    End Select
    
Next i

For the OR statement, you have to repeat the test:

If Mid(cellform, i, 1) = "=" Or Mid(cellform, i, 1) = "+"

Rgds, Geoff

Never test the depth of water with both feet

Help us to help you by reading FAQ222-2244 before you ask a question
 
Upvote 0 Downvote
oh ok i got the offset thing i was thinking of using something like

Sub alphabet()
Dim AlphabetLetter As String
Dim LetPos As Integer
Dim OffSetNumber As Integer
For LetPos = 65 To 90 ' upper case letters
AlphabetLetter = Chr(Ndx)
' do you query using FirstLetter
OffSetNumber = LetPos - 64
MsgBox OffSetNumber

Next LetPos

End Sub
 
Upvote 0 Downvote
Missed the G / H bit
If it is linear then this might do it for you
Code: 
myStr = "G"
myOffset = Range(myStr & "1").column - 1

Rgds, Geoff

Never test the depth of water with both feet

Help us to help you by reading FAQ222-2244 before you ask a question
 
Upvote 0 Downvote
bump - just in case you've missed my post - think we both posted our last ones at the same time !

Rgds, Geoff

Never test the depth of water with both feet

Help us to help you by reading FAQ222-2244 before you ask a question
 
Upvote 0 Downvote
Hey this is the code i got so far it works for everything from A - Z with or without $ and from lines 1-9

now i need to find a way for it to recognize sheets i am guessing i have to use the instr() funct.

and i need it to go 1-9999 again i am guessing i can find the length using instr()

if there is a better way pls tell me here is my code so far
Code: 
Sub test()
For Each c In Selection

  If c.HasFormula Then

    cellform = c.Formula
For i = 1 To Len(cellform)

If Mid(cellform, i, 1) = "=" Or Mid(cellform, i, 1) = "+" Or Mid(cellform, i, 1) = "*" _
    Or Mid(cellform, i, 1) = ")" Or Mid(cellform, i, 1) = "(" Then
    NewForm = NewForm & Mid(cellform, i, 1)
        
    ElseIf Mid(cellform, i, 1) = "$" Then
        If Mid(cellform, i + 2, 1) = "$" Then
             For LetPos = 65 To 90
                  If Mid(cellform, i + 1, 1) = Chr(LetPos) Then
                    AlphabetLetter = Chr(LetPos)
                    OffSetNumber = 65 - LetPos
                    cell_Ref = Mid(cellform, i, 4)
                    cell_Val = Range(cell_Ref).Offset(0, OffSetNumber).Value
                    NewForm = NewForm & cell_Val
                End If
            Next LetPos
i = i + 3
        Else
             For LetPos = 65 To 90
                  If Mid(cellform, i + 1, 1) = Chr(LetPos) Then
                    AlphabetLetter = Chr(LetPos)
                    OffSetNumber = 65 - LetPos
                    cell_Ref = Mid(cellform, i, 3)
                    cell_Val = Range(cell_Ref).Offset(0, OffSetNumber).Value
                    NewForm = NewForm & cell_Val
                End If
            Next LetPos
i = i + 2
        End If
    ElseIf Application.IsText(Mid(cellform, i, 1)) = True Then
        If Mid(cellform, i + 1, 1) = "$" Then
             For LetPos = 65 To 90
                  If Mid(cellform, i, 1) = Chr(LetPos) Then
                    AlphabetLetter = Chr(LetPos)
                    OffSetNumber = 65 - LetPos
                    cell_Ref = Mid(cellform, i, 3)
                    cell_Val = Range(cell_Ref).Offset(0, OffSetNumber).Value
                    NewForm = NewForm & cell_Val
                End If
            Next LetPos
i = i + 1
        Else
            For LetPos = 65 To 90
                If Mid(cellform, i, 1) = Chr(LetPos) Then
                    AlphabetLetter = Chr(LetPos)
                    OffSetNumber = 65 - LetPos
                    cell_Ref = Mid(cellform, i, 2)
                    cell_Val = Range(cell_Ref).Offset(0, OffSetNumber).Value
                    NewForm = NewForm & cell_Val
                End If
            Next LetPos
        End If
    End If
Next i

'Call Word and use put there instead but for testing purposes use debug.print
Cells(6, 3).Value = "Cell " & c.Address & ":" & c.Offset(0, -1).Value & NewForm

  End If
Next
End Sub




thnks
 
Upvote 0 Downvote
for sheets, you are probably best off testing for "'" as this should appear just before and just after the sheet name

Not sure there is a "better" way - to be honest- for what you are trying to achieve, I think we've managed some pretty slick code so far !!

Just as a note - did you try my column Letter to offset suggestion ??

Rgds, Geoff

Never test the depth of water with both feet

Help us to help you by reading FAQ222-2244 before you ask a question
 
Upvote 0 Downvote
ok the ' didn't appear as clearly as I thought it would

In formulae, shets are always surrounded by
' '!

so you can test for ' or ! - the only issue with ! would be that you would need to work backwards. Once you find a " ' ", you can use the instr function to determine the position of the next 1 and therefore the length of the sheet name - you can then do similar to what I did with the $ and add x to i to increment past the sheet name once you have grabbed it

Come to think of it, my assertion that this shouldn't be too tricky is coming back to haunt me !!;-)

Rgds, Geoff

Never test the depth of water with both feet

Help us to help you by reading FAQ222-2244 before you ask a question
 
Upvote 0 Downvote
i just realised another major problem....thinking this was not a good idea after all now:

it will not work for sum, if, vlookup etc.

i guess the sum would not be too hard to add in but the and for the others i guuess just copy and paste the formula how it is...
 
Upvote 0 Downvote
hmmm - does very much depend on what formulae are involved - as you say - sum wouldn't be too difficult because you will pull out the 1st and last cells and you just need to iterate between them - vlookup would be a problem but IFs and ANDs shouldn't be too much hassle......

Rgds, Geoff

Never test the depth of water with both feet

Help us to help you by reading FAQ222-2244 before you ask a question
 
Upvote 0 Downvote
thanks for your help will continue this tomorrow its late here 6.30 so much for 35 hour weeks in france
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

Andrzejek
  • Locked
  • Question Question
Open Word as ReadOnly
Replies
6
Views
1K
Chriss Miller
C
Andrzejek
  • Locked
  • Question Question
Hyperlinks in Word cut at #
Replies
2
Views
711
Andrzejek
Andrzejek
remeng
  • Locked
  • Solved
Run-time error '429': ActiveX component can't create object From Excel to Word
Replies
9
Views
2K
remeng
remeng
remeng
  • Locked
  • Solved
Combine two working macros into one for MS Word
Replies
7
Views
2K
strongm
strongm
remeng
  • Locked
  • Helpful tip Helpful tip
Microsoft Office 365 Word Macro Causing a Project is unviewable error
Replies
1
Views
2K
dhookom
dhookom

Part and Inventory Search

Sponsor

Back
Top