• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Issues with Macro and blank cells...

M

msquared99

Member
Here is the situation, I have a macro that writes an IF formula to the last row then does a copy/paste value. There will be an actual value in say cell P3, then several blanks and an actual value in cell P10 and so on. The number of blank cells between the cells with an actual value varies. The formula is =IF(P3="","",P3)

As we know with this type of formula when you copy/paste value then select cell P3 then use SHIFT+CTRL+Arrow Down, it will highlight all of the cells that once had a formula. COUNTBLANK indicates there are X number of blank cells.

I have a section of VBA code that uses Intersect. It errors out saying "No cells were found".

How do I get the cells inbetween that are supposed to be blank to actually be blank.

Below is the code used and I hope it is entered into the forum correctly.

Code: 
With Intersect(Range("P3").CurrentRegion, Range("P:P"))
  .SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=R[-1]C16"
  .Value = .Value
  End With
 
Hi, msquared99!

Give a try to this code, temporarily update it as follows:
Code: 
    With Intersect(Range("P3").CurrentRegion, Range("P:P"))
        .Select
        Stop
        .SpecialCells(xlCellTypeBlanks).Select
        Stop
        Selection.FormulaR1C1 = "=R[-1]C16"
        .Value = .Value
    End With

And check what ranges are selected at each stop.

BTW, this happens with cell P3 empty? If so, test it with any value in it.

Regards!
 
xlCellTypeBlanks and a cell with formula If(xxxx,"",XXX) is different.

In you situation, I would like to change your IF formula as..
=IF(P3="","",P3) to =IF(P3="",NA(),P3)
Now you can handle this with .SpecialCells function... like below.

Code: 
With Intersect(Range("P3").CurrentRegion, Range("P:P"))
  .SpecialCells(xlCellTypeFormulas, xlErrors).FormulaR1C1 = "=R[-1]C16"
End With
 
@msquared99, I'm not sure from your description if you are trying to set the blank values from VBA. If so, I believe you will need to set those cell values to "vbNullValue" in the code.

I hope that helps.

Ken
 
OK, I have tried the suggestions and end up with the same result.

SirJB7, at the first stop P3 thru the LastRow are selected. Then a run-time error 1004 No cells were found is displayed.

Same for DebraJ. If all those #N/A's between the names would only fill with the name in cell P3, P36 and so on I would be a happy man!

I did not try Ken's suggestion because I did not know where to put it in the code. I tried a few places but it did not work.

Thanks all.
 
Let me ask this: The key word I am looking for is "Employee Name" in column A, if "Employee Name" is found in column A I want in cell P3 that name to be populated there and in each below that until it finds another "Employee Name" then repeat until the end of the list.

Maybe this will help.
 
OK here is some new code and it finds each instance of the Employees Name and pastes it in column P. Now what I want to do is copy the name in say cell P3 down to the next name. So the name in cell P3 would be copied from cell P4 thru cell P24, then the name in cell P25 would be copied until the next name and so on.

Here is the code:

Code: 
Sub HR_AbsenceAudit()
  Dim FinalRow1 As Long
  
  Worksheets("HRIS").Activate
  
  FinalRow1 = Worksheets("HRIS").Range("H" & Rows.Count).End(xlUp).Row
  
  
  For i = 1 To FinalRow1
  If Sheets("HRIS").Range("A" & i).Value Like "Employee's Name:" Then
  Sheets("HRIS").Range("C" & i).Copy
  Range("P" & i).Select
  ActiveSheet.Paste
  Application.CutCopyMode = False
  End If
  Next i
  
  'With Intersect(Range("P3").CurrentRegion, Range("P:P"))
  '.SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=R[-1]C16"
  '.Value = .Value
  'End With
  
End Sub
 
@msquared99,

Please try the changes I have made below. The main problem was that you included too much in the "if" block.

Code: 
Sub HR_AbsenceAudit()
  Dim FinalRow1 As Long
  Dim sHR As Worksheet
  Dim rTest As Range, rEmpName As Range, rWriteName As Range
  Set sHR = Worksheets("HRIS")
  sHR.Activate
  FinalRow1 = sHR.Range("H" & Rows.Count).End(xlUp).Row
  Set rTest = sHR.Range("A1")
  Set rEmpName = sHR.Range("C1")
  Set rWriteName = sHR.Range("P1")
  For i = 1 To FinalRow1
      If rTest.Value Like "Employee's Name:" Then
        Set rEmpName = sHR.Range("C" & rTest.Row)
      End If
      rWriteName = rEmpName
      Set rTest = rTest.Cells(2, 1)
      Set rWriteName = rWriteName.Cells(2, 1)
  Next i
End Sub

Hope that helps.

Ken
 
Mike,

I believe the part that slows it down is the string comparison (Like"Employee's Name:"). VBA is not optimized for string operations in general, but there may be ways to improve the performance if your data allows.

For example, if the data in column A only contains "Employee's Name:" or blanks, then you could compare the length of the strings as shown below:

Code: 
If Len(Trim(rTest))>0 Then

It is difficult to optimize a solution with incomplete information about the data.

Hope that helps.

Ken
 
Hi Mike ,

Since you say the existing code is slow , try this :

Code: 
Sub HR_AbsenceAudit()
    Dim FinalRow1 As Long
    Dim sHR As Worksheet
    Dim rWriteName As Range

    Set sHR = Worksheets("HRIS")
    sHR.Activate
    FinalRow1 = sHR.Range("H" & Rows.Count).End(xlUp).Row
 
    Set rWriteName = sHR.Range("P1")
 
    rWriteName.Resize(FinalRow1).FormulaArray = "=INDEX(HRIS!$C$1:$C$50,LOOKUP(ROW(HRIS!$C$1:$C$50),IF(HRIS!$C$1:$C$50<>"""",ROW(HRIS!$C$1:$C$50))))"
    rWriteName.Resize(FinalRow1).Value = rWriteName.Resize(FinalRow1).Value
End Sub

I have assumed that column C has the employee names along with blanks.

Narayan
 
You must log in or register to reply here.
Back
Top