Microsoft Office Q&A: Ask anything (Word, Excel, PowerPoint, etc.)

Hey, I could really use some help here. I'm attaching a file as an example (it explains things a bit better in there). Basically, I'm trying to figure out how to copy content as Values.

http://www.datafilehost.com/d/3888a46f

When I pasted this text into the thread, several words were flagged with red underlines. Now, of course, they’ve just vanished from the Reddit post entirely.

hollowstag7 said:I really need some help here. I’m attaching a file as an example (it explains more in the sample itself). I'm trying to figure out how to copy content as Values.

http://www.datafilehost.com/d/3888a46f

You seriously think anyone is going to download an .exe file?

Benjamin Wilson7 said:I mean, nobody's really gonna download some random .exe file, you know?

But then again, where am I even supposed to put it? Like, which server would I use?

let's just say

Thanks.
Book3.xlsx

Try wrapping it in an IFERROR function (if you're using Microsoft Excel 2007) or maybe ISERROR if you're on the older Microsoft Excel 2003. For example:

=IFERROR(PRODUCT(A1*B1*C1)+PRODUCT(A1*D1*E1),0)

And hey, instead of that zero at the end, you could just use empty quotes "" if you prefer.

I'm curious, why is the original file acting up? Wouldn't it be way easier if you just used the one that actually works instead of this one?

Is everything formatted as numbers, or did some cells accidentally get set to text?

Benjamin Wilson7 said:I mean, is everything actually formatted as numbers, or did maybe some cells accidentally get set to text mode?

So when I try adding this IFERROR, I just keep getting that #NAME? error message.
Everything looks like it's formatted as a number with no decimals, so I don't know.
I guess I'll try to pull those two original lists out and post them here on the forum.

Maybe you were using periods instead of commas for your decimals? Just a guess.
Once you get those numbers in there, either I or someone else will take a look at it.

Benjamin Wilson7 said:Once you upload it, someone—either me or whoever else is around—will take a look at it.

Honestly, there isn't even much point in uploading it. I actually found a little error 🙂
I guess I messed around with the formula a bit just to make it look cleaner, and now it's kind of costing me some time.
In this specific case
For example:
if you have a cell containing this formula
1. =IF(P1="/";"1";IF(P1="//";"2";IF(P1="";"";"Wrong input"))) - copying this will result in an error
2. =IF(P1="/";"1";IF(P1="//";"2";IF(P1="";"0";"Wrong input"))) - when the cell is empty, copying results in a zero, which works fine

Henry Lewis37 said:Best regards,

I am looking to replicate this specific sparkline setup—essentially making it look identical to the attached image.

http://www.hichert.com/images/storie...klines_777.png

Refresh

Greetings, everyone.

I am reaching out because I could use some assistance with a specific task... I suspect this ought to be handled within Visual Basic, though I cannot say for certain myself.

"Write a program that identifies all even numbers located in the first column of a spreadsheet. The program should then display these identified numbers, one after another, in the second column."

Thank you in advance, and best regards.

Jesse Ortiz6 said:Regards,

I am looking for some assistance here if anyone is available... I suspect this needs to be handled via Visual Basic, though I can't say for certain.

"Write a program that identifies all even numbers in the first column of a spreadsheet. The program should list the found numbers sequentially in the second column."

Thanks in advance.

This gets the job done:

Sub Macro1()

Dim a, b, c
a = Worksheets(1).Columns(1).Cells.SpecialCells(xlCellTypeConstants).Count
c = 1
For b = 1 To a
If Value mod 2 = 0 Then Cells(b, 1).Value = Cells(b, 1).Value
Cells(c, 2).Value = Cells(b, 1).Value
c = c + 1
End if Next b end Sub
Next b

End Sub

electricdriver93 said:this should get the job done:

Sub Macro1()

Dim a, b, c
a = Worksheets(1).Columns(1).Cells.SpecialCells(xlCell TypeConstants).Count
c = 1
For b = 1 To a
If Value mod 2 = 0 Then Cells
Cells(c, 2).Value = Cells(b, 1).Value
c = c + 1
End If
Next b

End Sub

Best regards!!
Unfortunately, I can't get this to run... it keeps throwing a syntax error at me.

And it highlights this specific line in red:
a = Worksheets(1).Columns(1).Cells.SpecialCells(xlCell TypeConstants).Count

electricdriver93 said:this gets the job done:

Sub Macro1()

Dim a, b, c
a = Worksheets(1).Columns(1).Cells.SpecialCells(xlCellTypeConstants).Count
c = 1
For b = 1 To a
If Value mod 2 = 0 Then Cells(b, 1).Value = Cells(b, 1).Value
Cells(c, 2).Value = Cells(b, 1).Value
c = c + 1
End If
Next b

End Sub

It worked 🙂perfectly.

The error was actually just a syntax issue—I had a space between (xlCell and TypeConstants) that shouldn't have been there.

Thank you so much!!!!!!

My only remaining headache is that when I input a zero, the code treats it as an even number... is there a way to add a specific condition to prevent that??
Also, how would one define this to work on rows instead of columns??

Replace that first condition with this:

If Cells(b, 1).Value Mod 2 = 0 And Cells(b, 1).Value <> 0 Then

I see. So you just swap out the cell reference arguments.
Like this:

Sub Macro1()

Dim a, b, c
a = Worksheets(1).Rows(1).Cells.SpecialCells(xlCellTypeConstants).Count
c = 1
For b = 1 To a
If Cells(1, b).Value Mod 2 = 0 And Cells(1, b).Value <> 0 Then
Cells(2, c).Value = Cells(1, b).Value
c = c + 1
End If
Next b

End Sub

electricdriver93 said:just swap out that first condition for this one:

If Cells(b, 1).Value Mod 2 = 0 And Cells(b, 1).Value <> 0 Then

Right, so you just need to update the cell reference arguments
like this:

Sub Macro1()

Dim a, b, c
a = Worksheets(1).Rows(1).Cells.SpecialCells(xlCellTypeConstants).Count
c = 1
For b = 1 To a
If Cells(1, b).Value Mod 2 = 0 And Cells(1, b).Value <> 0 Then
Cells(2, c).Value = Cells(1, b).Value
c = c + 1
End If
Next b

End Sub

Thanks, legend... you really saved my skin here...😁😁😁😁

Jesse Ortiz6 said:Thanks, legend... you really saved my skin here...😁😁😁😁

Saved.😁
Don't mention it.

electricdriver93 said:saved my skin.😁
Don't mention it.

😵😵 Sorry... thanks for saving me there.😁

Can someone please just point me toward a simple download link for Microsoft Word 2007? I really need it.😍