It's VERY confusing trying to follow what you're doing...
it seems like you're using chr() functions to convert characters to STRINGS?
which doesn't make ANY sense.
I'd like to first address your solution, then ask what it is you're trying to
do? (lol)
For your code:
ActiveSheet.Cells(i, 2).Value = "=" & Chr(73) & Chr(70) & Chr(40) & Chr(68) &
NextRw & Chr(60) & Chr(34) & Chr(34) & Chr(44) & Chr(68) & NextRw & Chr(44) &
Chr(34) & Chr(34) & Chr(41)
The fact is: "=" is Chr(61)
So, if (for some odd reason) you're wanting to use character codes, why not
ALWAYS use character codes. .Value = Chr(61) & Chr(73) & Chr(40) ... ad
nauseum...
If you're wanting to use text strings, why not use text strings?
I test string is opened with a delimiter (") and closed with the same delimter.
"=" tells VBA that the = within the two quotes is to be treated as a text
string, not evaluated as an "operator".
"=" & Chr(73) & Chr(70)
is concatenating the string "=" with the character "I" and the character "F"
so why not use "=IF" ??
Say NextRw is 45
You expect the cell formula to read:
=IF(D45 < "", D45, "")
right?
In VBA you "build" a text string "=IF(D45 < "", D45, "")"
Except: to include a (") in a formula you have to 'double-up'.
so, ("") would cause it to display as (")
to get two of them ("") you have to do ("") and (""), or ("""")
So your code looks like:
ActiveSheet.Cells(i, 2).Value = "=IF(D45 < """", D45, """")"
But, you want to replace 45 with the current, runtime value if NextRw.
You simply start a text sting with ", stop it with ", concatenate the value of
NextRw, then switch back to concatenating text:
"=IF(D" & NextRw & " < """", D" & NextRw & ","""")"
so your VBA code looks like:
ActiveSheet.Cells(i, 2).Value = "=IF(D" & NextRw & " < """", D" & NextRw & ",
"""")"
---------------------------------------------------------------------------------------
Now, on to my second question: WHY?
Do you want the cell (cells(i,2)) to contain the value or do you really want it
to contain the formula?
why not make VBA do the work?
Why not use something like
If (Activesheet(cells(nextrw,"D").value <> "") _
and (Activesheet(cells(nextrw,"D").value > 0)) then
activesheet.cells(i,2).value = "=D" & nextrw
end if
Paul
-----------------------------------------
“Do all the good you can,
By all the means you can,
In all the ways you can,
In all the places you can,
At all the times you can,
To all the people you can,
As long as ever you can.” - John Wesley
-----------------------------------------
From: Mr_Bill <egge...@gmail.com>
>To: excel-macros@googlegroups.com
>Sent: Sunday, September 21, 2014 3:19 PM
>Subject: $$Excel-Macros$$ How to fix a vba code that returns a cell reference
>
>
>
>How this works is if data is input in the next available cell in a
column this code sees what cell it was and in another of area of the
sheet input the cell location like =YXX the XX being the cell number.
and Y being the Column
>
>using the nextrw from this line
>
>
>NextRw = ws.Range("A190").End(xlUp).Offset(2, 0).Row
I was able to come up with this
>
>
>ActiveSheet.Cells(i, 3).Value = "=" & Chr(68) & NextRw
Which does work but need to edit it so if the main cell is blank or has a -this
would return a blank I tried
>
>
>Evaluate("=IF(NextRw<"""",NextRw,"""")")
kind of a hail mary
>
>So went back to what worked before but the code looks ridiculous and
wonder can someone please fix this so it looks like a formula that is
easier to edit. Dont laugh too hard but here is what I came up with for a
simple formula that should come out looking like this sampel
=IF(YXX<"",YXX,"") the YXX are what the script finds using the NextRw
>
>
>
>
>ActiveSheet.Cells(i, 2).Value = "=" & Chr(73) & Chr(70) & Chr(40) & Chr(68) &
>NextRw & Chr(60) & Chr(34) & Chr(34) & Chr(44) & Chr(68) & NextRw & Chr(44) &
>Chr(34) & Chr(34) & Chr(41)
It does work just hard to read and edit.
>
>Copy of the stripped down WorkBook
>
--
>Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s
>=TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @
>https://www.facebook.com/discussexcel
>
>FORUM RULES
>
>1) Use concise, accurate thread titles. Poor thread titles, like Please Help,
>Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get
>quick attention or may not be answered.
>2) Don't post a question in the thread of another member.
>3) Don't post questions regarding breaking or bypassing any security measure.
>4) Acknowledge the responses you receive, good or bad.
>5) Jobs posting is not allowed.
>6) Sharing copyrighted material and their links is not allowed.
>
>NOTE : Don't ever post confidential data in a workbook. Forum owners and
>members are not responsible for any loss.
>---
>You received this message because you are subscribed to the Google Groups "MS
>EXCEL AND VBA MACROS" group.
>To unsubscribe from this group and stop receiving emails from it, send an
>email to excel-macros+unsubscr...@googlegroups.com.
>To post to this group, send email to excel-macros@googlegroups.com.
>Visit this group at http://groups.google.com/group/excel-macros.
>For more options, visit https://groups.google.com/d/optout.
>
>
>
--
Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s
=TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @
https://www.facebook.com/discussexcel
FORUM RULES
1) Use concise, accurate thread titles. Poor thread titles, like Please Help,
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get
quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security measure.
4) Acknowledge the responses you receive, good or bad.
5) Jobs posting is not allowed.
6) Sharing copyrighted material and their links is not allowed.
NOTE : Don't ever post confidential data in a workbook. Forum owners and
members are not responsible for any loss.
---
You received this message because you are subscribed to the Google Groups "MS
EXCEL AND VBA MACROS" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to excel-macros+unsubscr...@googlegroups.com.
To post to this group, send email to excel-macros@googlegroups.com.
Visit this group at http://groups.google.com/group/excel-macros.
For more options, visit https://groups.google.com/d/optout.
