Tag Info

Hot answers tagged

2

After much trying here's how to do it: IP's of servers are located in column B and C, then Username and Passwords are in column D and E. After double clicking the IP the correct username and password credentials are stored, then RDP is launched. This is confirmed to be working! Thanks everyone. Private Sub Worksheet_BeforeDoubleClick(ByVal Target As ...


2

Lock the cells to prevent Selection or Modify Protect the worksheet Have the UserForm code: a. unprotect the worksheet b. unlock the cell c. update the cell d. relock the cell e. reprotect the worksheet.


2

This should do what you are looking for: The formula in B2 is special because it starts you out: =MID($A2, 1, B1) but then C2 can be copied down the entire 18 remaining columns D2->LastCol2: =MID($A2,SUM($B$1:B$1)+1, C$1) Then copy the whole row down and the formulas should autofill correctly.


2

From what I understand of your question, you're missing a key piece: Ranges have properties like Left, Top, Right, and Width just like images do. Here's a function that takes in a Range object as a parameter, prompts the user to select images, and then fits the images into that range. Key Point: Based on your request, it is written so that aspect ratio is ...


2

Reference: http://www.cpearson.com/excel/Events.aspx and http://www.excel-easy.com/vba/events.html For more google Worksheet_Change among Excel events. Idea: In the Worksheet_Change event, check if either of the cells can be recalculated, and do it if possible. Note that the whole tasks is a bit more complicated than filling in a couple formulas though. ...


2

The Intersect is not working because each parameter must have a common cell or cells. It is not an OR situation; it is an AND. You need to essentially use Target AND (NTit OR NInf OR NDat). This can be accomplished by combining Intersect with Union. If Not Intersect(Target, Union(NTit, NInf, NDat)) Is Nothing Then This looks like it might be from a ...


2

You need to use the Range Object rather than a String Dim WS As Worksheet, NTT As Range Set WS = ActiveSheet Set NTT = WS.Range("$A$1:$U$154") If Not Intersect(Target, NTT) Is Nothing Then............ You were very close!


1

You've got to get away from using .Select as a method of referencing cells, cell ranges and even worksheets. Each can be directly referenced in its own way. See How to avoid using Select in Excel VBA macros from that other site. Here is some code that uses direct referencing while accomplishing the goals you've set out. Sub Reference_Lookup_Paste() Dim ...


1

Your diagnosis is correct -- VBA ignores hidden/unhidden status when parsing cell references with .Cells. A brute-force cell-counting method is the only thing I've found to work: Dim iter As Long, findCount As Long, workCel as Range ' This is okay as long as you are guaranteed only to have one header row. Set workCel = Worksheets("MySheetName").Cells(2, 1) ...


1

@Brian, Thanks again -- your answer and reference to Areas led me to look into ways to obtain the number of the visible row which corresponds to the Arg2 value in the series. Did some more research and found a solution that seems similar in principle, but counts rows rather than cells (http://www.ozgrid.com/forum/showthread.php?t=23611). This seems to ...


1

Help to help yourself. Find "Record new macro" in your version of Excel. Close to it you find "Use relative references" - select this if it is relevant for the task at hand. Now click on "Record new macro" and type in a name and optionally add a keyboard shortcut for launching the macro. Do the steps that you need in the macro. Click "Stop ...


1

"I'm trying to search my Excel worksheet for any cell that has a value that begins with a number followed by a period, but this isn't working. Does anyone know why not?" A period . has a special meaning in a regular expression, it means "Matches any single character". If you want to match a literal period . you need to escape the special meaning. \ is used ...


1

Put this VBA code in the worksheet: Private Sub Worksheet_Change(ByVal Target As Range) Dim wks As Worksheet Set wks = ActiveSheet cellempty = False i = 1 'Check the max. number of rows While cellempty = False a = Cells(i, 2) If a = "" Then finalrow = i - 1 cellempty = True End If i = i + 1 Wend 'Going row by row For i = 1 ...


1

Something like this would work Sub test() Dim n As Integer n = Cells(1, 1) If n >= 1 Then Rows("10:" & 10 + n).Insert shift:=xlDown Rows("20:" & 20 + n).Insert shift:=xlDown End If End Sub You could also do it like this - Sub test() Dim n As Integer n = Cells(1, 1) If n >= 1 Then For i = 1 To n ...


1

Place one of the limits in B1 and the other in B2 In A1 enter: =RANDBETWEEN(MIN($B$1,$B$2),MAX($B$1,$B$2)) and copy down thru A10 Put a button somewhere on the sheet and assign this macro to it: Sub marine() Application.Calculate End Sub



Only top voted, non community-wiki answers of a minimum length are eligible