1

Working on this process where I have to take formulas (mostly VLOOKUP formulas) listed in the top row of this section, perform and find/replace, remove the apostrophe placed in front of the formula and drag down formula to appropriate rows. The process is as follows:

  1. Copy the formulas in cells AT1:BZ1 and paste them in AT4:BZ4
  2. If cell contains "Last Weeks Summary Report Name", replace it with "This Weeks Summary Report Name". (35 replacements)
  3. If cell contains "Last Weeks Final Report Name", replace it with "This Weeks Final Report Name." (3 replacements)
  4. If cell has an apostrophe as its first character, remove it.
  5. Click and Drag all formulas to Row 5000.
  6. Copy all cells in the array and paste them as values (to reduce size of file)

I am able to perform these steps individually, but I run into difficulties when trying to combine the steps of the process. My problem I think lies with the order in which I perform these tasks and not having the knowledge of how to nest IF statements within a WITH statement. Additionally, I'm having difficulties in using the Immediate window with the WITH statement. Here's what I have so far:

Dim wb_Final As Workbook, nameFinal As String
Set wb_Final = Workbooks.Open(Filename:=Final_Directory)
nameFinal = wb_Final.Name

Dim wb_Summary As Workbook, nameSummary As String
Set wb_Summary = Workbooks.Open(Filename:=Summary_Directory)
nameSummary = wb_Summary.Name

wb_Summary.Sheets("Sheets 1").Activate
Range("AT1:BZ1").Copy
Range("AT4:BZ4").PasteSpecial xlPasteAll
Application.CutCopyMode = False
' This is where my problem is:
For Each C In Worksheets("Sheets 1").Range("AT4:BZ4").Cells
'If cell is blank
'Else
     'If LEFT(cell, 1) = "'"
   Replace(cell, "'", "")

etc..

I have tried a different approach to this process that can be seen here. This process makes a lot more sense to me, but generates an error that I can't figure out.

I have also thought about defining the range and create a DO UNTIL IS NOTHING and loop it through.

Any help would be much appreciated :)

braX
  • 11,506
  • 5
  • 20
  • 33
iMILES
  • 11
  • 1
  • 1
    In your loop where you're having problems, don't you mean `If C` not `If cell`? You don't use the variable `cell`, but `C` instead. `If C = "" // Else // If Left(c,1) = "'" // Replace(C,"'","") // ...` ? – BruceWayne May 15 '18 at 20:14

0 Answers0