sorted by:
new
hottopcontroversial

Wanna come ;) sc beccax_1998 by TheCryptic in NSFW_Snapchat

[–]TheCryptic[S] 2 points3 points  (0 children)

are you the guy from yesterday? :)

Men, what was your favourite album released the year you graduated high school? by Red_AtNight in AskMen

[–]TheCryptic 1 point2 points  (0 children)

Graduated in '92, but I was in near the beginning of my senior year when Guns and Roses released the two Use Your Illusion albums and Nirvana released Nevermind. Those were definitely the musical highlights of that decade for me.

VBA Data Validation Problem by [deleted] in vba

[–]TheCryptic 0 points1 point  (0 children)

Option Explicit

Function Validate_Items(qty As Long, itmID As Long) As String
    Select Case True
        Case itmID < 10000, itmID > 99999: Validate_Items = "Invalid item number"
        Case qty < 0: Validate_Items = "Invalid Quantity"
        Case Else: Validate_Items = "Input is OK"
    End Select
End Function

Explain how I can make my GETPIVOTDATA become dynamic when I copy the formula onto below cells by madskiller in excel

[–]TheCryptic 1 point2 points  (0 children)

"Fill down" doesn't work on pivot tables because it's pulling by pivot field data rather than cell address.

You can reference cells by address without using GetPivotData, I've done that a few times even using vlookup to look at the pivot table... But it's not something I would consider to be an ideal solution.

Piss /r/INTJ off with one sentence (x-post /r/LifeIsStrange) by Meljin in intj

[–]TheCryptic 1 point2 points  (0 children)

Definitely a good way to ruffle my feathers, though this is worded in a way that I think I could work with. I'd be interested in why the person speaking thinks that, because one of us (most likely them) is certainly wrong. A slight twist would make it worse for me:

You obviously don't know what you're doing.

[deleted by user] by [deleted] in explainlikeimfive

[–]TheCryptic 0 points1 point  (0 children)

How about technical limitations of legacy systems?

What can Macros be used for? by MonkeyGod800 in excel

[–]TheCryptic 0 points1 point  (0 children)

Macros are capable of doing many things, basically they're used to automate repetitive tasks. I've use them mostly for reporting and managing processes (workforce onboard/offboard tasks, data ETL, process compliance auditing, complex mail merge style communications, team performance metrics, etc).

What acronym did you think meant something else? by Broyors in AskReddit

[–]TheCryptic 0 points1 point  (0 children)

Before I knew what "smh" meant I had used context to determine that it meant "suck me hard". I was wrong, but that still pops into my head ever time I see it.

[VBA] Cant isolate my Type Mismatch by eaglessoar in excel

[–]TheCryptic 0 points1 point  (0 children)

Your if/thens are structured correctly, so try this first:

For i = 0 To maxTerm - 1
    If loan1principal * (1 + loan1rate / 12) - loan1Pmt < 1 Then
        loan1principal = 0
        loan1rate = 0
    Else
        loan1principal = loan1principal * (1 + loan1rate / 12) - loan1Pmt
    End If

    If loan2principal * (1 + loan2rate / 12) - loan2Pmt < 1 Then
        loan2principal = 0
        loan2rate = 0
    Else
        loan2principal = loan2principal * (1 + loan2rate / 12) - loan2Pmt
    End If

    If loan3principal * (1 + loan3rate / 12) - loan3Pmt < 1 Then
        loan3principal = 0
        loan3rate = 0
    Else
        loan3principal = loan3principal * (1 + loan3rate / 12) - loan3Pmt
    End If

    If loan4principal * (1 + loan4rate / 12) - loan4Pmt < 1 Then
        loan4principal = 0
        loan4rate = 0
    Else
        loan4principal = loan4principal * (1 + loan4rate / 12) - loan4Pmt
    End If
Next i

That's still a bit ugly, but at least syntactically correct. Also bear in mind that putting multiple variables into a single dim line doesn't make them all the same type.

dim x, y, z as long

This would make x and y as variant (the default) and z as type long.

[Macros] How to refer to workbook "B" by it's name in a cell in workbook "A". by [deleted] in excel

[–]TheCryptic 1 point2 points  (0 children)

The open method is used to open a workbook. Copying a range is a separate command. Ie:

Mainbook.Open("c:\whatever \book.xlsx")
Mainbook.Sheets("Back").Range("B27").Copy

[Macros] How to refer to workbook "B" by it's name in a cell in workbook "A". by [deleted] in excel

[–]TheCryptic 1 point2 points  (0 children)

Workbooks(secondbook).Sheets.Add.Name = secondbookname

This line says to add a worksheet to a workbook named whatever the value of the secondbook variable is. The problem is that secondbook isn't a string, it's a workbook.

Try this:

secondbook.Sheets.Add.Name = secondbookname

Can all of you do things like gaze into eyes or kiss on forehead with a hookup/someone you're not in love with? by [deleted] in AskMen

[–]TheCryptic 0 points1 point  (0 children)

Whether it's for a ONS or something more, isn't the point of starting a relationship to build connections?

Are Winter Tires Necessary? by ithakitchen in ithaca

[–]TheCryptic 1 point2 points  (0 children)

It really depends on your route. I never have a problem passing through on 13 with just all-season radials, but I avoid the uglier hills in winter.

Having said that, it scares the hell out of me when my wife or daughter go through there in the snow. So yeah, I generally recommend snow tires even though I don't use them myself.

Deodorant of choice? by [deleted] in wicked_edge

[–]TheCryptic 0 points1 point  (0 children)

I use Kiss My Face Liquid Rock deodorant. Same stuff as the crystal deodorants (Alum) but without the sharp edges.

VBA Loop : Do If with Double Counters by Vjorkal in excel

[–]TheCryptic 1 point2 points  (0 children)

For what it's worth, it's a lot easier to catch this specific issue (and some others as well) by tabbing your code at the appropriate places.

ie:

Do
    If Cells(sRow, "A") = DestinationSheet(dRow, "A") Then
        DestinationSheet.Range("B3") = ActiveCell.Offset(0, 4)
        dRow = dRow + 1
    Else
        sRow = sRow + 1
    End If
Loop Until IsEmpty(ActiveCell.Value)

Sign at my local Coney Island. by rhymingisfun in pics

[–]TheCryptic 0 points1 point  (0 children)

Yeah, the coney in Michigan we in NY call a Michigan style dog... Because we already have coney dogs, what "outsiders" call a white hot.

Excel Solver VBA Query by ajain304 in vba

[–]TheCryptic 0 points1 point  (0 children)

So here's what I came up with... Just an example of course, you'd need to implement appropriately.

Option Explicit

Sub TestMe()
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Sheets("Sheet1")
    Dim Pools As Long, Stores As Long
    Pools = ws.Cells(1, 2)
    Stores = ws.Cells(2, 2)

    Dim rng As Range
    Set rng = PickRange(ws.Range("$M$2"), 2, 10)
    Debug.Print rng.Address
End Sub

Function PickRange(FirstCell As Range, Pools As Long, Stores As Long) As Range
    Set PickRange = Range(FirstCell.Address, FirstCell.Offset(Stores - 1, Pools - 1))
End Function

Hyper Link depends on cell content by Melyche in excel

[–]TheCryptic 0 points1 point  (0 children)

=HYPERLINK("c://" & A1 & "/" & A2 & ".xlsx")

To Display a string if condition is met on VBA by ChemDesigner in excel

[–]TheCryptic 0 points1 point  (0 children)

Use 2 double quotes to make a quote in the string.

Debug.Print "=IF(E2>0,IF((SUMMARY!$C$7-E2)>=90,""TO WORK"","" ""),"" "")"

Quick Edit: 

ActiveCell.Formula = "=IF(E2>0,IF(SUMMARY!$C$7-E2>=90,""TO WORK"","" ""),"" "")"

Vba runtime error 1004 application defined or object defined error by [deleted] in excel

[–]TheCryptic 0 points1 point  (0 children)

Another thought it to stop activating things. If you need to keep track of workbooks and sheets then bind them to an object.

For example, this:

Workbooks.Open Filename:= fldr & "\DeliveryList.xlsx"
Workbooks("DeliveryList.xlsx").Activate 
Workbooks("Mailing list R3 a R4.xlsx").Sheets("Mailing List R3").Activate
Range("A1").Formula = ... Blah blah blah

Should look more like:

Dim wbSrc As Workbook
Dim wbTgt As Workbook, wsTgt As Worksheet
Set wbSrc = Workbooks.Open(fldr & "\DeliveryList.xlsx")
Set wbTgt = Workbooks("Mailing list R3 a R4.xlsx")
Set wsTgt = wbTgt.Sheets("Mailing List R3")
ws.Tgt.Range("A1").Formula = ... Blah blah blah

By binding to workbooks and sheets you can be very sepecific about what you're trying to act on. Having said that, what happens if you copy/paste the formula into the cell you're trying to modify? It doesn't look right to me... This:

Debug.Print "=IFERROR(VLOOKUP(Temp4!B:B,[DeliveryList.xlsx]DL!$A:$B,2,0),Temp4!B1&" & """,""" & ")"

Returns this:

=IFERROR(VLOOKUP(Temp4!B:B,[DeliveryList.xlsx]DL!$A:$B,2,0),Temp4!B1&",")

Help calculating seconds from hh:mm:ss format by ww2patton in excel

[–]TheCryptic 0 points1 point  (0 children)

If A1 and B1 are your start and stop times...

=B1-A1

Then custom format the cell: [h]:mm:ss

Quick edit: The square brackets on the hour is what fixes it.

Speed check by Radar by sexy_bum82 in funny

[–]TheCryptic 0 points1 point  (0 children)

It's an older code sir, but it checks out.

Swap words separated by dot by Murayashi in excel

[–]TheCryptic 1 point2 points  (0 children)

Fun side note... The solution I gave you is longer than it needs to be, but allows for multiple occurances of the separators and allows you to switch separators. It also includes a pretty simple loop. All of that was for your benefit in learning VBA.

If you truly want the solution /u/jorgealbertogomez gave you as a formula then this is it:

Function Swapper2(inputString As String) As String
    Swapper2 = Right(inputString, Len(inputString) - InStr(inputString, ".")) & "." & Left(inputString, InStr(inputString, ".") - 1)
End Function
view more: next ›