Replacing Formulas with Code

**pauldaddyadams** · 11-21-2014, 09:08 AM

Hi,

I have a problem with a report I am converting from PDF to Excel.

The problem is, it doesnt recognise certain data correctly and as a result columns are merged which distorts the data.

In the attached I have shown the orignal report in columns A:H along with what I need in J:Q. I have used formulas for this but ideally would I would like is a code that would work and do this for me, can anyone help please?

One thing to note is that I wouldnt want the data in column J:Q - I would like it to replace the orignal data table A:H

Please note in my data i have manually inputted the sub totals manually - ideally I would like the code to do this. A similar code was supplied on a different post for this here (I just need to work out how it works)

HTML Code:

Any suggestions are very much welcomed.

**jaslake** · 11-21-2014, 01:30 PM

Hi Paul

This Code in the attached appears to handle all but the Sub Totals. I can't make sense of where you've placed Sub Totals. Please explain the logic of the Sub Totals and I'll look at it.

Option Explicit

Sub Format_Me()
  Dim ws As Worksheet
  Dim Rng As Range, cel As Range, cel1 As Range
  Dim LR As Long
  Set ws = Sheet8
  With ws
    LR = .Cells.Find("*", .Cells(.Rows.Count, .Columns.Count), SearchOrder:=xlByRows, _
                     SearchDirection:=xlPrevious).Row

    Set Rng = .Range(.Cells(14, "B"), .Cells(LR, "B"))
    For Each cel In Rng
      If IsNumeric(cel.Value) Then
        cel.Resize(1, 6).Cut Destination:=cel.Offset(0, 1)
      End If
    Next cel

    Set Rng = .Range(.Cells(14, "A"), .Cells(LR, "A"))
    For Each cel1 In Rng
      If Len(cel1) > 2 Then
        cel1.Offset(0, 1).Value = cel1.Value
        cel1.Value = Split(cel1.Value, " ")(0)
        cel1.Offset(0, 1).Value = Replace(cel1.Offset(0, 1), cel1.Value, " ", , 1)
      End If
    Next cel1
  End With
End Sub

**pauldaddyadams** · 11-24-2014, 09:42 AM

Hi John

Thanks for looking into this for me.

The code doesnt work from row 20 down. What I need is the values shifted accross one column to the right and then in column B for it to strip out the customer name excluding the first two digits.

Also, could it be applied to the orignal sheet (active sheet)?

Regarding the sub totals:
What I would like a code to do is look in column A for the words “sub total” and if found I would like it to sum the rows above until if finds the previous subtotal. The columns range is C:H

Then if it finds “Net Settlement Total” I would like it to sum all the sub totals just generated (above)

Does that help?

**jaslake** · 11-24-2014, 11:41 AM

Hi Paul

Where will I find this, Column A? It does not appear in your Sample File.

“Net Settlement Total”

**pauldaddyadams** · 11-24-2014, 12:10 PM

Hi John

This was missed on the sample upload. I have now included it along with the desired results

Paul

**jaslake** · 11-24-2014, 12:36 PM

Hi Paul

When you say this

could it be applied to the original sheet (active sheet)?

Do you mean you wish to see "Old" and "New" on the SAME Sheet or should the Code be run on Columns A through H?

**pauldaddyadams** · 11-24-2014, 12:41 PM

Hi,

It should be run through columns A to H (the new part is only for reference of what I am trying to achieve in cells A:H).

Regarding the same sheet I see some codes take the oringal sheet and then make the amendments to the new sheet however I would like it applied to the original sheet.

**jaslake** · 11-25-2014, 12:50 PM

Hi Paul

Try the Code in the attached. CTRL + x will fire the Code on Worksheet APPO24.

Sheet1 is included for testing only and can be deleted. Let me know of issues.

**pauldaddyadams** · 11-28-2014, 11:01 AM

Hi Jaslake

I have only just seen your reply, normally I rely on a notifcation which I didnt recieve.

This is great!! So far so good but will test fully

**pauldaddyadams** · 11-28-2014, 11:44 AM

Hi,

It does indeed work great for the dummy data but not for my actual template but I think it will work with a couple of tweeks.

Is there any way after the current code is run it could delete the rows (after row 20) if:

Row is blank
Row contains (exactly in column A) "App024", ,"Retail, "Excel VAT", "Business", "Inter", "Period", "Code", "Page", "End"

I think the code for the above is similar to this below (cut from a previous code) :

If WorksheetFunction.CountA(Rows(N)) = 0 Then
        Rows(N).Delete
    End If
    If InStr(Cells(N, 1), "APP024") > 0 Or InStr(Cells(N, 1), "Retail") > 0 Or InStr(Cells(N, 2), "Excl VAT") > 0 Or InStr(Cells(N, 1), "Business") > 0 Or InStr(Cells(N, 1), "Inter") > 0 Or InStr(Cells(N, 1), "Period") > 0 Or InStr(Cells(N, 1), "Code") > 0 Or InStr(Cells(N, 1), "Page") > 0 Or InStr(Cells(N, 1), "End") > 0Then
        Rows(N).Delete
    End If

Then I would need the header rows inputted and formatted:

.Rows("18:20").Insert Shift:=xlDown

Range("A20") = "Code"
Range("B20") = "Business"
Range("C20") = "Ex VAT £"
Range("D20") = "VAT £"
Range("E20") = "INC VAT £"
Range("F20") = "Ex VAT £"
Range("G20") = "VAT £"
Range("H20") = "INC VAT £"


Range("C19:E19").Merge
Range("F19:H19").Merge

Range("C19:E19") = "Contributions From"
Range("F19:H19") = "Contributions To"

Range("A19:H20").Font.Bold = True

Can you help in combining all codes please?

**jaslake** · 11-28-2014, 12:40 PM

Hi Paul

Please attach a File that includes ALL the Code you reference here; that will help me understand a bit better what you're trying to do.

the code for the above is similar to this below (cut from a previous code)

It seems to me that these Code additions will need to be run BEFORE the current Code, not after

after the current code is run it could delete the rows (after row 20) if:
Row is blank
Row contains (exactly in column A) "App024", ,"Retail, "Excel VAT", "Business", "Inter", "Period", "Code", "Page", "End"

This does not appear in your most current File

“Net Settlement Total”

but "Grand total" does...so, which is it, or could it be either?

**jaslake** · 11-28-2014, 04:03 PM

Hi Paul

I'm a bit confused as to your expected output. Show me what the first 25-30 Rows will look like of your expected output.

If you show me this you can forget about this

Please attach a File that includes ALL the Code you reference here; that will help me understand a bit better what you're trying to do.
the code for the above is similar to this below (cut from a previous code)

Also, what are the Rand Formulas...I'm assuming they should be replaced with Values...is this correct?

Replacing Formulas with Code

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Replacing Formulas with Code

Re: Replacing Formulas with Code

Re: Replacing Formulas with Code

Re: Replacing Formulas with Code

Re: Replacing Formulas with Code

Re: Replacing Formulas with Code

Re: Replacing Formulas with Code

Re: Replacing Formulas with Code

Re: Replacing Formulas with Code

Re: Replacing Formulas with Code

Re: Replacing Formulas with Code

Re: Replacing Formulas with Code

Thread Information

Users Browsing this Thread

Similar Threads

Replacing Multiple Formulas code

Replacing all formulas in a folder of workbooks

replacing values in linked formulas

Replacing 0 with blank in formulas

Replacing Formulas with Values

Bookmarks

Bookmarks

Posting Permissions