Posts by restlessheron19

Kyle Johnson2 said:If your data is sitting in range A1:A??, give this formula a shot in cell B1:
Code:

=IF(A1="";"";IF(A1<2000;2000;IF(A1>=2000;(ROUNDDOWN(A1+2000;-3));"")))

Thanks, John
🙂

Excel 2007, 2013.
I'm dealing with a measurement issue involving millimeters. If the value is under 2000 mm, I need it to round up to 2000 mm. If it's over 2000 mm, it should round up to 4000 mm. Essentially, I want everything rounded up to the nearest 2000 mm increment once it passes that first threshold.
Any help would be appreciated.

Kyle Johnson2 said:Google works wonders
Deleting a VBA module via a VBA button

Google, sure... I found the exact same—or something very close—code online... no pop-ups involved... but I can't figure out why I'm getting an error?
Run-time ERROR 1004 Method
"VBE" of object "_Application" failed

Is it actually possible to use a button to wipe out a Module or even multiple Modules directly from an Excel sheet within the Visual Basic Editor inside the same file? If there's a way to pull this off... does anyone have a lead on where I might find code for a button like that? Or maybe someone here already knows how?

Benjamin Wilson7 said:Maybe it’s something like this.

Yeah, turns out I left some comments in one of those tables I wanted to hide, which was preventing everything from being tucked away up to column HA. Thanks, all sorted now! Works fine with 🙂

Benjamin Wilson7 said:No, the validation frames are actually "in front of the columns."

Right, I see that the validation frames are sitting ahead of the columns—that wasn't what I meant.

Inside the specific columns (cells) I'm trying to hide, I have a table (http://www.microsoft.com - Pulling range data from a row into a dependent drop-down list), plus several other tables where I perform copy-pasting operations (swapping out certain bits of data)—moving info from one table to another using the same logic (calling code) as this validation hiding method. Could any of that be interfering with my ability to hide these columns this way? Actually, whenever I try to hide those exact same columns manually, an error pops up: "Cannot move objects off sheet." So, something in that list above is definitely causing a conflict. An "object"—what exactly counts as an object in Excel!?

code 1
Code:

I have a checkbox running code 1 (which calls code 2 from another sheet, specifically Columns("BM : DP").Select)—that part works fine; the columns hide as expected.
Now, I'm trying to expand that selection to
Code:

but it keeps throwing an error..





Any idea why? Does it matter that those columns are actually the ones I'm trying to hide?

Awesome! It works in Notepad too, plus you can use the concatenate thus
formula. Yeah, I realized it’s doable in Excel as well.
Thanks for the quick reply... appreciate it.
🙂

Hey there.

Is there a way to export this?
I have a block of data sitting in several rows and columns on an Excel worksheet. My goal is to save this information as a text file where everything stays in the same order as it appears in Excel, but without any spaces or gaps between the individual data points. Currently, when I try using the standard export options—like Unicode text or tab-delimited—it keeps inserting those gaps between the values. Basically, I want to skip the spacing during the export process so that every piece of data sits right next to the next one.

Not like this:
123 / 456 / 789

But more like this:
123/456/789

Kyle Johnson2
Yeah, I tried using that exact same code, but the command just wouldn't copy the values...
Not sure what happened there. Maybe it had something to do with how many formulas were being converted at once, or maybe I just missed something in my own logic...
Anyway, I decided to scrap my original plan of copying entire columns altogether.

For anyone else here planning on grabbing this macro... just a heads-up that the version quoted above is CopyRangeToFirstEmptyRow... but with a slight tweak. You'll need to change the Offset (0,0)... to (1,0)...

That aside, the macro worked wonders for me! Thanks again.

And yes, it definitely handles the value pasting...

When I record a macro, I select Copy... then Paste Special -> Values.
The recorded macro doesn't actually paste "values" when I run it.
How do I get this to work?
I want to copy entire columns—say, "K:N"—as values from List1 over to List2 (specifically into column "A:D") using a macro.

Any suggestions, examples, or links?

I found this

Sub KopirajRangeUprviPrazanRed()

Worksheets("List1").Range("A1:D65536").Copy 'selects and copies the specified data range on List1
Worksheets("List2").Cells(Rows.Count, "A").End(xlUp).Offset(0, 0).PasteSpecial pasta : = xlPasteValues 'finds the first empty row in column A on List2 and pastes the range data
Sheets("List1").Select 'returns focus to List1
Application.CutCopyMode = False 'clears the clipboard selection
Range("B2").Select 'places cursor on cell B2 on List1
List1 end Sub

Walter Jackson48 said:So, what does that "10" actually represent in your formula?
If you’re trying to multiply 10 by B4, just try using
10*B4 (instead of 10;B4

The number 10 represents the percentage increase required.
The formula I mentioned... it spits out a result, but it's wrong.
When I type the formula in manually, it works fine.

Yeah, both formulas work once they're fixed the way you suggested.
Swap the semicolon for * and +.

Thanks.

I've run into a bit of a situation here..

Columns 1 through 5 are where I paste my data—specifically using Paste Values.
Columns 6 and 7 contain formulas that process those values from columns 1, 2, 3, 4, and 5.
When the data is pasted into those first five columns, the formula in column 6 updates instantly with the right value.
But the formula in column 7 just sits there at zero. Why does one work while the other stays stuck?
Both formulas are technically correct, but it’s like the second one needs a manual "nudge" to actually trigger.
For instance, if I click into one of the cells—say, column 2—and re-enter the same number, the formula suddenly works. But obviously, that's not a viable workflow.
What am I missing?

The code:

This formula worked fine..

The code:

This one failed to calculate..

Donna Thomas57 said:Ugh, I tried that. It just doesn't work!

What do you mean it doesn't work?? I just did it and it worked fine!
The instructions clearly say to watch your steps... Just follow them precisely and you'll be fine..

1. Put your cursor at the end of page 4
2. Insert > Break > Next Page
3. View > Header and Footer
4. On the floating menu, uncheck "Link to Previous"
5. Click the icon on the floating menu for "Insert Page Number" — don't type anything manually
6. Click the "Format Page Number" icon on that same floating menu
7. Choose where you want the numbering to begin — set "Start At" to 1
8. Double-click outside the header and you're done

Afterward, double-click back into the header — put your cursor in the field where the page number is (don't type anything) — now you can adjust the position. Align left, center, or right

Donna Thomas57 said:🙂🙂
I'm hoping someone can walk me through this... if anyone could just explain how to actually do it...
I have the English version of Microsoft Word 2003 installed, if that matters.

Here's a link WORD - STARTING PAGE NUMBERING ON THE THIRD (OR FOURTH) PAGE. It's got a pretty solid explanation...

electricdriver93 said:Sub Macro1()

Dim a As Variant
a = Range("a1").Value
Range("b1").AddComment range
Range("b1").Comment.Visible = False '(or True - depending on if you want the comment visible or not)
Range("b1").Comment.Text Text:=a
End Sub

Nice... yeah... it works...
To add to that... I’d actually prefer to have all my comments sitting on a single sheet... say in column A... one comment per row. Then, each specific comment would be called into cells on other sheets where they're needed...
Why go about it that way? Just so I can manage them more easily...

Maybe, if we could tweak this macro to handle a larger batch of comments at once...
The workflow would be simple: I'd use one macro to wipe all existing comments from the various sheets... then use this new macro to drop the updated comments right back into their designated spots...

Is this actually doable?
The text from cell New York City...pulling it into a comment...in cell B1?....
So, the comment would end up living in cell B1..

Walter Jackson48 said:Here’s the formula I’m using—it has about 30 conditions and works fine on my machine:

The formula works fine... it does the job... just like the previous one did... it worked for me too...
but it doesn't actually solve my problem.

I posted an example "above" showing how I need it to function...
Maybe this example helps if you know your way around this stuff
There are two lists in the sample...
List1...works perfectly with 8 functions
But List1 (2), which uses 15 functions... isn't working at all.

restlessdrifter12 said:Every table I insert just sticks itself right to the one before it.

Try this: insert two tables.
2. When you hover over a table, look for that little handle in the top-left corner... the cursor icon will change. Click that corner, hold it down, and just drag the table wherever you want it to go. Simple as that.

Here’s an example:

Walter Jackson48 said:then you just grab the border editing tool—it’s that little dropdown menu with all the different box options.

Just one more thing so you don't go hunting for it... check the ribbon under Tables & Borders.

Walter Jackson48 said:Try starting a new stretch with "&" after every eight functions.
For instance, if you have 24 IF functions, you'd need two "&" connectors (that's just the ampersand, no quotes needed).

I did... that was the formula in the previous post... doesn't matter. The clicker isn't working right now, but it'll get there eventually.

I've been reading up on Excel Macros... specifically how to trigger them.
Is it actually possible to run a macro directly from a cell? 🙂
Like this... if a cell has a formula such as =IF(A1>1, run macro, etc...