Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old September 25th 07, 07:54 PM posted to microsoft.public.excel.worksheet.functions
UB UB is offline
external usenet poster
 
First recorded activity by ExcelBanter: Jan 2007
Posts: 120
Default Error type mismatch in writing macro

Hi
I have a formula in cell - =IF(J45="","",(J45+181)), where if j45 is blank,
the cell h45 is blank or h45 has a new date, the format of the coloumn "H"
is date.

I my VB code I am using this column to satify a condition and my code is
For rwIndex = 4 To 40
Year(Worksheets("Sheet1").Cells(rwIndex, 12).Value) = Year(Mydate)
Then
Worksheets("Sheet1").Cells(rwIndex, 12).Interior.Color = RGB(0, 0, 255)

I am getting Runtime error '13' - Type Mismatch, because few cells blank,
because of the cell formula IF(J45="","",(J45+181))

Please advise how can I correct the VB code

  #2   Report Post  
Old September 25th 07, 08:07 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 4,624
Default Error type mismatch in writing macro

Your cell isn't blank. It has a formula in it that's returning a null
string as a value... One alternative:

Dim rCell As Range
For Each rCell in Worksheets("Sheet1").Range("L4:L40")
With rCell
If IsDate(.Value) Then
If Year(.Value) = Year(Mydate) Then
.Interior.Color = RGB(0, 0, 255)
End If
End If
End With
Next rCell

In article ,
ub wrote:

Hi
I have a formula in cell - =IF(J45="","",(J45+181)), where if j45 is blank,
the cell h45 is blank or h45 has a new date, the format of the coloumn "H"
is date.

I my VB code I am using this column to satify a condition and my code is
For rwIndex = 4 To 40
Year(Worksheets("Sheet1").Cells(rwIndex, 12).Value) = Year(Mydate)
Then
Worksheets("Sheet1").Cells(rwIndex, 12).Interior.Color = RGB(0, 0, 255)

I am getting Runtime error '13' - Type Mismatch, because few cells blank,
because of the cell formula IF(J45="","",(J45+181))

Please advise how can I correct the VB code

  #3   Report Post  
Old September 26th 07, 12:48 PM posted to microsoft.public.excel.worksheet.functions
UB UB is offline
external usenet poster
 
First recorded activity by ExcelBanter: Jan 2007
Posts: 120
Default Error type mismatch in writing macro

Hi
It worked, Thanks

"JE McGimpsey" wrote:

Your cell isn't blank. It has a formula in it that's returning a null
string as a value... One alternative:

Dim rCell As Range
For Each rCell in Worksheets("Sheet1").Range("L4:L40")
With rCell
If IsDate(.Value) Then
If Year(.Value) = Year(Mydate) Then
.Interior.Color = RGB(0, 0, 255)
End If
End If
End With
Next rCell

In article ,
ub wrote:

Hi
I have a formula in cell - =IF(J45="","",(J45+181)), where if j45 is blank,
the cell h45 is blank or h45 has a new date, the format of the coloumn "H"
is date.

I my VB code I am using this column to satify a condition and my code is
For rwIndex = 4 To 40
Year(Worksheets("Sheet1").Cells(rwIndex, 12).Value) = Year(Mydate)
Then
Worksheets("Sheet1").Cells(rwIndex, 12).Interior.Color = RGB(0, 0, 255)

I am getting Runtime error '13' - Type Mismatch, because few cells blank,
because of the cell formula IF(J45="","",(J45+181))

Please advise how can I correct the VB code




Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Type Mismatch Error - Help Please Launchnet Excel Worksheet Functions 5 July 20th 07 04:35 AM
Macro error type mismatch Jurassien Excel Discussion (Misc queries) 3 February 23rd 07 08:14 PM
Type Mismatch Error David Excel Discussion (Misc queries) 2 December 11th 05 04:46 PM
Visual Basic macro run time error(13) type mismatch Paul Excel Discussion (Misc queries) 0 October 25th 05 07:28 AM
Why type mismatch - R/T error 13 Jim May Excel Discussion (Misc queries) 5 January 9th 05 06:45 PM


All times are GMT +1. The time now is 12:23 PM.

Powered by vBulletin® Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
Copyright 2004-2019 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017