Formula to word file

[RamziSaab]

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

 

[xlbo]

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

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

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

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

 

[RamziSaab]

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

 

[RamziSaab]

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

 

[xlbo]

Ramzi,
this should help for point 1:

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

 

[RamziSaab]

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

 

[xlbo]

Missed the G / H bit
If it is linear then this might do it for you

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

 

[xlbo]

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

 

[RamziSaab]

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

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

 

[xlbo]

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

 

[xlbo]

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

 

[RamziSaab]

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

 

[xlbo]

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

 

[RamziSaab]

thanks for your help will continue this tomorrow its late here 6.30 so much for 35 hour weeks in france