[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 347
  • Last Modified:

Excel VB Find text in Hidden Cell

This clunky little code does a couple things...namely moving and renaming some files as well as doing a Find/Replace.  It worked great until recently when the Find/Replace steps quit replacing text in hidden cells.  It replaces text in visble cells, so I know the Find/Replace is working...

I'm hitting some dead ends elsewhere...
Sub Replacer()

 

Dim tPath As String, tFile As String, ReplaceWhat As String, ReplaceWith As String, ReplaceWhat2 As String, ReplaceWith2 As String

Dim wb As Workbook

Dim ws As Worksheet

Dim strFileName As String

Dim strFile As String

Dim pathDest As String

Dim strD As Date

Dim strMonth As Integer

Dim strNewMonth As Integer

'Deal with this in case current date is January or February:

Select Case Month(Date)
    Case 1
        strMonth = 11
    Case 2
        strMonth = 12
    Case Else
        strMonth = Month(Date) - 2
End Select

Select Case Month(Date)
    Case 1
        strNewMonth = 12
    Case Else
        strNewMonth = Month(Date) - 1
End Select

'Change as required.  This is the month name change

ReplaceWhat = MonthName(strMonth)

ReplaceWith = MonthName(strNewMonth)


'Change as required.  This is to change the forecast name at beg of each quarter

If strNewMonth < 4 Then

    ReplaceWhat2 = "Forecast 4"
    
    ReplaceWith2 = "Forecast 1"
    
End If

If strNewMonth > 3 And strNewMonth < 7 Then

    ReplaceWhat2 = "Forecast 1"

    ReplaceWith2 = "Forecast 2"
    
End If

If strNewMonth > 6 And strNewMonth < 10 Then

    ReplaceWhat2 = "Forecast 2"

    ReplaceWith2 = "Forecast 3"
    
End If

If strNewMonth > 9 And strNewMonth <= 12 Then

    ReplaceWhat2 = "Forecast 3"

    ReplaceWith2 = "Forecast 4"
    
End If


'This is what we add to the name of each file

strFileName = Left(ReplaceWith, 3)

 

'this is the source file location

strFile = tPath & tFile

 

'Final destination for updated files

pathDest = "C:\Documents and Settings\rstolly\Desktop\Final\"

 

'The path where your files are saved

tPath = "C:\Documents and Settings\rstolly\Desktop\Source\"

 

'the *.* is all file types, *.xls will give you all xls files, *Reports.xls will give you all files ending with Reports.xls etc

tFile = Dir(tPath & "*.xls")

 

Do While Len(tFile) > 0

 

    Set wb = Workbooks.Open(tPath & tFile)

 

    For Each ws In wb.Worksheets

 

   'Assumes you have all data in the first sheet. Can be amended to loop through all sheets in workbook

   'Set ws = wb.Sheets(1)

    ws.UsedRange.Replace ReplaceWhat, ReplaceWith
    ws.UsedRange.Replace ReplaceWhat2, ReplaceWith2

    Next

    wb.SaveAs (pathDest & strFileName & Mid(tFile, 4, Len(tFile)))

    wb.Close True

    tFile = Dir

 

 

 

Loop



End Sub

Open in new window

0
bsncp
Asked:
bsncp
  • 4
  • 4
1 Solution
 
Saurabh Singh TeotiaCommented:
There you go,use this one...
Saurabh...

Sub Replacer()
 
  
 
Dim tPath As String, tFile As String, ReplaceWhat As String, ReplaceWith As String, ReplaceWhat2 As String, ReplaceWith2 As String
 
Dim wb As Workbook
 
Dim ws As Worksheet
 
Dim strFileName As String
 
Dim strFile As String
 
Dim pathDest As String
 
Dim strD As Date
 
Dim strMonth As Integer
 
Dim strNewMonth As Integer
 
'Deal with this in case current date is January or February:
 
Select Case Month(Date)
    Case 1
        strMonth = 11
    Case 2
        strMonth = 12
    Case Else
        strMonth = Month(Date) - 2
End Select
 
Select Case Month(Date)
    Case 1
        strNewMonth = 12
    Case Else
        strNewMonth = Month(Date) - 1
End Select
 
'Change as required.  This is the month name change
 
ReplaceWhat = MonthName(strMonth)
 
ReplaceWith = MonthName(strNewMonth)
 
 
'Change as required.  This is to change the forecast name at beg of each quarter
 
If strNewMonth < 4 Then
 
    ReplaceWhat2 = "Forecast 4"
     
    ReplaceWith2 = "Forecast 1"
     
End If
 
If strNewMonth > 3 And strNewMonth < 7 Then
 
    ReplaceWhat2 = "Forecast 1"
 
    ReplaceWith2 = "Forecast 2"
     
End If
 
If strNewMonth > 6 And strNewMonth < 10 Then
 
    ReplaceWhat2 = "Forecast 2"
 
    ReplaceWith2 = "Forecast 3"
     
End If
 
If strNewMonth > 9 And strNewMonth <= 12 Then
 
    ReplaceWhat2 = "Forecast 3"
 
    ReplaceWith2 = "Forecast 4"
     
End If
 
 
'This is what we add to the name of each file
 
strFileName = Left(ReplaceWith, 3)
 
  
 
'this is the source file location
 
strFile = tPath & tFile
 
  
 
'Final destination for updated files
 
pathDest = "C:\Documents and Settings\rstolly\Desktop\Final\"
 
  
 
'The path where your files are saved
 
tPath = "C:\Documents and Settings\rstolly\Desktop\Source\"
 
  
 
'the *.* is all file types, *.xls will give you all xls files, *Reports.xls will give you all files ending with Reports.xls etc
 
tFile = Dir(tPath & "*.xls")
 
  
 
Do While Len(tFile) > 0
 
  
 
    Set wb = Workbooks.Open(tPath & tFile)
 
  
 
    For Each ws In wb.Worksheets
 
  
 
   'Assumes you have all data in the first sheet. Can be amended to loop through all sheets in workbook
 
   'Set ws = wb.Sheets(1)
 
    ws.Cells.Replace ReplaceWhat, ReplaceWith
    ws.Cells.Replace ReplaceWhat2, ReplaceWith2
 
    Next
 
    wb.SaveAs (pathDest & strFileName & Mid(tFile, 4, Len(tFile)))
 
    wb.Close True
 
    tFile = Dir
 

 
Loop
 
 
 
End Sub

Open in new window

0
 
bsncpAuthor Commented:
Thanks, Saurabh.  I tried that code and the result is the same.  It only replaced the text in visible cells...
0
 
Saurabh Singh TeotiaCommented:
It should not, are you sure the replace is in the same format..??
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
bsncpAuthor Commented:
I'm not sure what you mean by the replace being in the same format.  Are you asking if the hidden cell is formatted as 'text'?  

To test what was happening, I opened the file directly and tried to do a Find/Replace manually.  It does not work on the hidden cell.  If I then unhide the cell, the Find/Replace does work.  that's what has lead me to believe find/replace has stopped working on the hidden cells.
0
 
bsncpAuthor Commented:
Okay...my mistake here.  The hidden rows are actually part of a set of rows that have been Grouped and Outlined.  So to test, I created a new test file containing Grouped and Outlined rows, and doing a manual Find/Replace works just fine.  

So it makes me think something unusual is going on in the file itself, something that makes the Grouped and Outlined Rows unavailable for the Find/Replace operation...

the user of the file tells me she added a zero check that would suppress lines that were all zeroes.  Maybe this is a clue?
0
 
Saurabh Singh TeotiaCommented:
Well, i wont be able to comment till the time i see the file, Also are you sure in the rows where dates are hidden they and replace format are same..?
0
 
bsncpAuthor Commented:
Yes, the format is the same.  The word "November" is spelled out in the cell being searched.  It's the same in that hidden cell as it is in the visible cell.  

I guess I could do a workaround that doesn't hide the rows until after I've done the Find/Replace.  How would I tell Excel to hide any rows where a certain cell value = 0?  For example,

If A4 = 0, then hide Row A. ...and do that for each row in the spreadsheet...
0
 
Saurabh Singh TeotiaCommented:
Assuming you want to start from row-4 till the last row of A Column, then use something like this...
Saurabh...

dim lrow as long,srow as long
lrow=cells(cells.rows.count,"A").end(xlup).row

srow=4

do until srow>lrow

if cells(srow,"A").value=0 then 
Rows(srow).Hidden = True
end if
srow=srow+1

loop

Open in new window

0

Featured Post

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

  • 4
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now