Re: $$Excel-Macros$$ Help - Recorded Formula Not Ending Up In Cell Correctly
Hi, I really appreciate the assistance, but that has nothing to do with my
issue. The formula I'm using is the recorded version of the working formula.
But thanks!
John
On Tuesday, April 3, 2018 at 7:02:47 AM UTC-4, Aas mh wrote:
> Hi You are using a slightly wrong one,
>
> try to change R[-7] to R[-6] like below:
> =IF(AND(R[-6]C[-5]<>R[-7]C[-5].. and rest yourself.
>
> On Sat, Mar 31, 2018 at 4:55 AM, Johnny >
> wrote:
>
>> Hi All, this is the strangest problem I've ever had with inserting a
>> formula into a cell with VBA. This is the formula that I am trying to
>> insert into cell K2:
>>
>>
>> =IF(AND(B2<>B1,B2<>B3),"ONLY",IF(AND(B2=B1,B2<>B3),"LAST",IF(B3=B2,TEXT(I3-J2,"h:mm"
>>
>>
>> It records like this:
>>
>> "=IF(AND(R[-7]C[-5]<>R[-6]C[-5],R[-5]C[-5]<>R[-6]C[-5]),""ONLY"",IF(AND(R[-7]C[-5]=R[-6]C[-5],R[-6]C[-5]<>R[-5]C[-5]),""LAST"",IF(R[-5]C[-5]=R[-6]C[-5],TEXT(R[-5]C[2]-R[-6]C[3],""h:mm"""
>>
>>
>> If I manually insert the formula into K2, it works perfectly and I can
>> fill down, and it works perfectly all the way down the column.
>>
>> Here's the problem, this is how it ends up in cell K2:
>>
>>
>> =IF(AND(G1048571<>G1048572,G1048573<>G1048572),"ONLY",IF(AND(G1048571=G1048572,G1048572<>G1048573),"LAST",IF(G1048573=G1048572,TEXT(N1048573-O1048572,"h:mm"
>>
>> I have never seen anything like this in all the time I've been tinkering
>> with VBA. Not only do the Column references get messed up, but what's up
>> with the numbers in the hundreds of thousands?
>>
>> I use the syntax of Range("K2").FormulaR1C1 =
>> (and it works with every other formula I've
>> ever tried). There's just something funky about this particular formula I'm
>> trying to insert. Please help me, I thank you in advance!
>>
>> John
>>
>> --
>> 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...@googlegroups.com .
>> To post to this group, send email to excel-...@googlegroups.com
>> .
>> Visit this group at https://groups.google.com/group/excel-macros.
>> For more options, visit https://groups.google.com/d/optout.
>>
>
>
>
> --
> Thanks & Regards.
> Aas Mohd.
> *https://www.youtube.com/watch?v=sfInP8sZ3DQ
> <https://www.youtube.com/watch?v=sfInP8sZ3DQ>*
>
>
--
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 https://groups.google.com/group/excel-macros.
For more options, visit https://groups.google.com/d/optout.
$$Excel-Macros$$ Re: Help - Recorded Formula Not Ending Up In Cell Correctly
I included a small file, with sample data in it, and the macro I'm working on. -- 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 https://groups.google.com/group/excel-macros. For more options, visit https://groups.google.com/d/optout.
$$Excel-Macros$$ Re: Filling in blank cells with names
Hi David, thanks for responding! When I run this macro, it actually does fill the column down until it hits the second non-blank then stops. But it did fill in the first name. Do you have an idea why it stops after the first non-blank? On Monday, March 12, 2018 at 1:15:24 PM UTC-4, who wrote: > Sorry, copy and paste missed the "S" in first line of Sub and simply says > "ub". > Thanks, David > > On Sunday, March 11, 2018 at 9:20:14 AM UTC-7, Johnny wrote: > >> Hi all, >> >> As an example, I have the name "John" in cell C4. Further down in C8 I >> have the name "Jeff". Using a VBA script, I'd like to loop down the C >> column, and fill in the blank cells between these names with the last name >> found. >> >> Before: >> >> John >> >> >> >> Jeff >> >> >> Tony >> >> After: >> >> John >> John >> John >> John >> Jeff >> Jeff >> Jeff >> Tony >> >> Any help would be appreciated. >> >> Thanks! >> >> John >> > -- 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 https://groups.google.com/group/excel-macros. For more options, visit https://groups.google.com/d/optout.
$$Excel-Macros$$ Filling in blank cells with names
Hi all, As an example, I have the name "John" in cell C4. Further down in C8 I have the name "Jeff". Using a VBA script, I'd like to loop down the C column, and fill in the blank cells between these names with the last name found. Before: John Jeff Tony After: John John John John Jeff Jeff Jeff Tony Any help would be appreciated. Thanks! John -- 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 https://groups.google.com/group/excel-macros. For more options, visit https://groups.google.com/d/optout.
$$Excel-Macros$$ Trouble with VBA line break
I'm having trouble breaking this line in VBA.
Range("T2").FormulaR1C1 =
"=COUNTIF(RC17,""<94"")+COUNTIF(RC18,""<89"")+COUNTIF(RC19,""<90"") _
+COUNTIF(RC12,"">3.46"")+COUNTIF(RC13,"">2.82"")+COUNTIF(RC14,"">2.44"")+COUNTIF(RC15,"">2.31"")+COUNTIF(RC16,"">2.67"")"
I put the space and underscore after that parenthesis, and I tried breaking
it after the "+" sign
I'm wondering if the problem is the type of statement I'm writing? Any help
would be much appreciated
John
--
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 https://groups.google.com/group/excel-macros.
For more options, visit https://groups.google.com/d/optout.
Re: $$Excel-Macros$$ Need Help please - Type Mismatch on rows insert macro
Thank you Paul, I've got a huge mental block on this type of expression. I
appreciate the help!
On Friday, June 2, 2017 at 11:56:24 AM UTC-4, Paul Schreiner wrote:
>
> Let's say that you have 100 rows of data.
>
> "bottom" ends up being 100.
> so your Rows statement results in:
> Rows(R:100).insert shift:=xldown
>
> I think it needs to be
> Rows(100:100).insert shift:=xldown
>
> so you'd use:
> Rows(r & ":" & r).Insert Shift:=xlDown
>
> *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*
> -
>
>
> On Friday, June 2, 2017 10:07 AM, Johnny >
> wrote:
>
>
> Trying to insert a blank row between each line. The line starting with:
> Rows is getting a type mismatch error.
>
>
> -
> Dim bottom As Integer
> Dim r As Integer
>
> bottom = Cells(Rows.Count, "A").End(xlUp).Row
>
> For r = bottom To 2 Step -1
> Rows("r:" & r).Insert Shift:=xlDown
> Next r
>
> --
> I appreciate the help!
>
> --
> 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...@googlegroups.com .
> To post to this group, send email to excel-...@googlegroups.com
> .
> Visit this group at https://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 https://groups.google.com/group/excel-macros.
For more options, visit https://groups.google.com/d/optout.
$$Excel-Macros$$ Need Help please - Type Mismatch on rows insert macro
Trying to insert a blank row between each line. The line starting with:
Rows is getting a type mismatch error.
-
Dim bottom As Integer
Dim r As Integer
bottom = Cells(Rows.Count, "A").End(xlUp).Row
For r = bottom To 2 Step -1
Rows("r:" & r).Insert Shift:=xlDown
Next r
--
I appreciate the help!
--
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 https://groups.google.com/group/excel-macros.
For more options, visit https://groups.google.com/d/optout.
Re: $$Excel-Macros$$ Help with Proper Syntax for Range function with variables
Thank you Ashish it works perfectly. I will save this info so I don't
repeat my question in the future. Much appreciated. -- John
On Friday, November 4, 2016 at 4:08:49 PM UTC-4, ashish wrote:
> try
>
> Range("A" & bottom & ":M" & bottom).Select
>
> On Sat, Nov 5, 2016 at 1:34 AM, Johnny >
> wrote:
>
>> I searched for the answer to this before posting. I know what I'm trying
>> to do can work, it's just that the syntax is kicking my butt
>>
>> I find the bottom row number (in my case 462) using Column E in this
>> example, and I'd like to select the Range from Column A to Column M with
>> that row number.
>>
>> Dim bottom As Integer
>> Dim r As Integer
>> bottom = Cells(Rows.Count, "E").End(xlUp).Row
>> *Range("A & bottom:" & M & bottom).Select* < This is the offending
>> error. I know it's a misplaced colon or quote...
>>
>> *** I'm basically needing to write: *range(A462:M462).select*, but using
>> the variable "bottom"
>>
>> Thanks for your help!
>>
>> John
>>
>> --
>> 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...@googlegroups.com .
>> To post to this group, send email to excel-...@googlegroups.com
>> .
>> Visit this group at https://groups.google.com/group/excel-macros.
>> For more options, visit https://groups.google.com/d/optout.
>>
>
>
>
> --
> *Regards*
>
> *Ashish Koul*
>
>
> *Visit*
> http://www.excelvbamacros.in
> Like Us on Facebook <https://www.facebook.com/excelvbacodes>
> Join Us on Facebook <http://www.facebook.com/groups/163491717053198/>
>
>
> P Before printing, think about the environment.
>
>
>
--
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 https://groups.google.com/group/excel-macros.
For more options, visit https://groups.google.com/d/optout.
$$Excel-Macros$$ Help with Proper Syntax for Range function with variables
I searched for the answer to this before posting. I know what I'm trying to
do can work, it's just that the syntax is kicking my butt
I find the bottom row number (in my case 462) using Column E in this
example, and I'd like to select the Range from Column A to Column M with
that row number.
Dim bottom As Integer
Dim r As Integer
bottom = Cells(Rows.Count, "E").End(xlUp).Row
*Range("A & bottom:" & M & bottom).Select* < This is the offending
error. I know it's a misplaced colon or quote...
*** I'm basically needing to write: *range(A462:M462).select*, but using
the variable "bottom"
Thanks for your help!
John
--
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 https://groups.google.com/group/excel-macros.
For more options, visit https://groups.google.com/d/optout.
$$Excel-Macros$$ VBA - checking for success of a Global Replace
One of the first steps in my macro does a global replace, looking for "BLKY046**" and replace it with "LKAREA". I would like to use this Replace as a means to keep the macro from running again if it has already been run once. Basically, if the Replace can't find "BLKY046**" it means I already ran the macro on the file and don't want to perform the subsequent statements. Here's my macro, hopefully this is an easy ask. I thank you for your help: Sub LK_CLEANUP() Cells.Replace What:="BLKY046**", Replacement:="LKAREA", LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=False 'Start from the bottom of the file, delete all rows that don't have the word "LKAREA" in column A bottom = Cells(Rows.Count, "A").End(xlUp).Row For r = bottom To 1 Step -1 If Cells(r, 1) <> "LKAREA" Then Cells(r, 1).EntireRow.Delete End If Next r End Sub -- 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 https://groups.google.com/group/excel-macros. For more options, visit https://groups.google.com/d/optout.
Re: $$Excel-Macros$$ Copying data into a cell with Conditional Formatting already applied
Paul, thanks for taking a look. I start by copying the contents of the MASTER SAVE tab and paste values into Cell A1 of Worksheet tab. I then run the macro NCFL_cleanup to get rid of everything on that page except the single column of data I want to use. The idea is then to paste the column of data into Column F of the SCRAP tab. In there I already have Cond Formatting underneath, and some formulas to the right of that data. I see what you mean by text versus numbers etc. One issue I have is that some of the data I want to paste in is percentages and others are whole numbers. On Tuesday, April 12, 2016 at 1:39:51 PM UTC-4, Paul Schreiner wrote: > Can you attach an example? > It's difficult to guess what MIGHT be happening based on your description. > > When you "copy" the data into the cells, are you pasting the entire > contents or have you tried to "Paste Special -> Values" > ? > > Are the values numeric or alpha-numeric? > > It's possible when you copy and paste the cells, you're getting "text" > pasted into the cells. > But when you re-type the value, Excel re-interprets the value and changes > the cell content type. > > For instance, if your conditional formatting tests the value for >1 > and you copy/paste a value that is "2", > well, a TEXT value of "2" is not greater than a NUMERIC value of 1, so the > condition is false. > but, if you TYPE a 2, then Excel recognizes that 2 > 1 and the condition > is true. > > *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* > - > > > On Tuesday, April 12, 2016 1:23 PM, Johnny > > wrote: > > > > Hi All, I searched for an answer on this first but I'm hoping for some > easy, good advice. > > Using VBA, I have preloaded some cells with formula-based conditional > formatting. When I copy a column of data into these cells, the conditional > formatting does not take effect. > Also, formulas in neighboring cells to the right do not change based upon > this copied-in data. If I go to one of these copied-in cells and just > re-type the data, the Conditional Formatting and neighboring formulas work > as written. > > I tried Data->Refresh All, and that didn't seem to work. Thanks for any > advice on this (hopefully easy) issue. -- John > -- > 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...@googlegroups.com . > To post to this group, send email to excel-...@googlegroups.com > . > Visit this group at https://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
$$Excel-Macros$$ Copying data into a cell with Conditional Formatting already applied
Hi All, I searched for an answer on this first but I'm hoping for some easy, good advice. Using VBA, I have preloaded some cells with formula-based conditional formatting. When I copy a column of data into these cells, the conditional formatting does not take effect. Also, formulas in neighboring cells to the right do not change based upon this copied-in data. If I go to one of these copied-in cells and just re-type the data, the Conditional Formatting and neighboring formulas work as written. I tried Data->Refresh All, and that didn't seem to work. Thanks for any advice on this (hopefully easy) issue. -- John -- 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 https://groups.google.com/group/excel-macros. For more options, visit https://groups.google.com/d/optout.
$$Excel-Macros$$ Running a macro on multiple worksheets (code included)
Hi all, I've read through several threads to come up with the code below,
regarding having a macro operate on all the worksheets in a workbook.
I hope I'm missing something simple here. The workbook has 14 tabs, but the
code only runs in the tab where I initiate the macro. Any help is very much
appreciated.
Thanks
John
---
Sub WEBADDcheck_up()
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Dim WS As Worksheet
For Each WS In ActiveWorkbook.Worksheets
'DeleteRowsWebadd
Rows("61:72").Delete shift:=xlUp
Rows("59:59").Delete shift:=xlUp
Rows("53:54").Delete shift:=xlUp
Rows("39:51").Delete shift:=xlUp
Rows("30:33").Delete shift:=xlUp
Rows("26:28").Delete shift:=xlUp
Rows("7:17").Delete shift:=xlUp
Rows("1:1").Delete shift:=xlUp
Rows("1:1").Select
With Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlBottom
.Orientation = 70
.IndentLevel = 0
.ReadingOrder = xlContext
End With
Cells.Select
With Selection.Font
.Name = "Calibri"
.Size = 10
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
.TintAndShade = 0
.ThemeFont = xlThemeFontMinor
End With
ActiveSheet.[a1] = ActiveSheet.Name
Cells(1, 1).Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.Orientation = 0
End With
With Selection.Font
.Name = "Calibri"
.FontStyle = "Bold"
.Size = 14
End With
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorAccent5
.TintAndShade = 0.599963377788629
.PatternTintAndShade = 0
End With
Cells.Select
Cells.EntireColumn.AutoFit
Next WS
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = False
End Sub
--
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.
Re: $$Excel-Macros$$ Delete the Rows Between the Last Row in Column A and the Last Row of Column B
Hi Don, thanks for responding. I inserted one of your examples:
Rows(lastrowB& ":" & lastrowC).Delete
and I'm getting "Compile Error Syntax Error".
I tried moving around the "" and such, but couldn't figure it out.
Thanks
Johnny
On Mar 9, 3:13 pm, "dguillett1" wrote:
> try
>
> Rows(lastrowB& ":" & lastrowC).Delete
> or
> Rows(lastrowB).resize(lastrowC-lastrowb).Delete
>
> Don Guillett
> Microsoft MVP Excel
> SalesAid Software
> dguille...@gmail.com
>
>
>
> -Original Message-
> From: Johnny
> Sent: Thursday, March 08, 2012 7:08 PM
> To: MS EXCEL AND VBA MACROS
> Subject: $$Excel-Macros$$ Delete the Rows Between the Last Row in Column A
> and the Last Row of Column B
>
> For example: The last row of data in column B is row 20. The last row
> of data in Column C is 30. I'm trying to delete rows 21 through 30.
> The script is bombing on the rows.select line saying "Run time error
> 13, type mismatch"
>
> My code attempt:
>
> --
> Sub cleanup7()
>
> Dim lastrowB As Integer
> Dim lastrowC As Integer
>
> lastrowB = Cells(Rows.count, "B").End(xlUp).Row + 1
> lastrowC = Cells(Rows.count, "C").End(xlUp).Row
>
> Rows("lastrowB:lastrowC").Select
> Selection.Delete Shift:=xlUp
>
> End Sub
>
>
> Any help would be greatly appreciated.
>
> Thanks
> Johnny
>
> --
> FORUM RULES (986+ members already BANNED for violation)
>
> 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) Cross-promotion of, or links to, forums competitive to this forum in
> signatures are prohibited.
>
> NOTE : Don't ever post personal or confidential data in a workbook. Forum
> owners and members are not responsible for any loss.
>
> ------
> To post to this group, send email to excel-macros@googlegroups.com- Hide
> quoted text -
>
> - Show quoted text -
--
FORUM RULES (986+ members already BANNED for violation)
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) Cross-promotion of, or links to, forums competitive to this forum in
signatures are prohibited.
NOTE : Don't ever post personal or confidential data in a workbook. Forum
owners and members are not responsible for any loss.
--
To post to this group, send email to excel-macros@googlegroups.com
$$Excel-Macros$$ Delete the Rows Between the Last Row in Column A and the Last Row of Column B
For example: The last row of data in column B is row 20. The last row
of data in Column C is 30. I'm trying to delete rows 21 through 30.
The script is bombing on the rows.select line saying "Run time error
13, type mismatch"
My code attempt:
--
Sub cleanup7()
Dim lastrowB As Integer
Dim lastrowC As Integer
lastrowB = Cells(Rows.count, "B").End(xlUp).Row + 1
lastrowC = Cells(Rows.count, "C").End(xlUp).Row
Rows("lastrowB:lastrowC").Select
Selection.Delete Shift:=xlUp
End Sub
Any help would be greatly appreciated.
Thanks
Johnny
--
FORUM RULES (986+ members already BANNED for violation)
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) Cross-promotion of, or links to, forums competitive to this forum in
signatures are prohibited.
NOTE : Don't ever post personal or confidential data in a workbook. Forum
owners and members are not responsible for any loss.
--
To post to this group, send email to excel-macros@googlegroups.com
Re: $$Excel-Macros$$ Formula Needed to Extract Data
Ash Can you post the VBA code? I downloaded the example but it is not in the workbook. I need to use a similar code for data extraction also. This code might help me, too. Thanks Johnny -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com <><><><><><><><><><><><><><><><><><><><><><> Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel
$$Excel-Macros$$ Re: Excel 2010 problem - Need to work with 15 + digits
DEF Highligh the column then go to "DATA" then "Text to Column". You will have 2 choices, Delimited or Fixed. I don't know how the data is so I am guessing no commas. I would choose Delimited. Click next. Click tab as I think there is no spaces. If so then click spaces too. click next then the data should be highlighted. In the column data format click "Text". Then finish. That should solve it. Johnny -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com <><><><><><><><><><><><><><><><><><><><><><> Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel
