.Offset(, 5)
Means 5 columns from column Q.
Select Q1 and hit the right arrow 5 times to V1
Posts by davesexcel
-
-
Using column Q to indicate the non blank cells, you can loop through column V to find values.
Display MoreCode- Sub GetData()
- Dim rng As Range, c As Range, LstRw As Long
- Set rng = Range("Q40:Q430").SpecialCells(xlCellTypeConstants, 23).Offset(, 5)
- Application.EnableEvents = False
- For Each c In rng.Cells
- If c <> "" Then
- LstRw = Cells(Rows.Count, "C").End(xlUp).Row + 1
- Cells(LstRw, "C") = c
- Cells(LstRw, "F") = c.Offset(, 3)
- Cells(LstRw, "K") = c.Offset(, 8)
- Cells(LstRw, "M") = c.Offset(, 10).Value
- End If
- Next
- Application.EnableEvents = True
- End Sub
-
MyRng.EntireRow.Copy _
wbTo.Range("D" & lDestLastRow)You can't copy an entire row to column D as that would make the data go outside the worksheet.
Edit: Ha, guess this was already said
-
You can also create an variable for the results of TextBox1 & TextBox2, set the variable to zero if the textbox is blank.
Display MoreCode -
cells E69:F69 need to be merged.
-
Both have different data but they both are on the same row.
You can have both column A & B in the same listbox, in your properties, change to column count=2 and change the range to include A2:B4
Here is the code, it just needs to be in the Command Button
Display MoreCode- Private Sub CommandButton1_Click()
- Dim LstRw As Long
- For x = 0 To ListBox1.ListCount - 1
- If ListBox1.Selected(x) = True Then
- With Sheet2
- .Cells(.Rows.Count, 1).End(xlUp).Offset(1) = ListBox1.List(x, 0)
- .Cells(.Rows.Count, 2).End(xlUp).Offset(1) = ListBox1.List(x, 1)
- End With
- End If
- Next x
- Unload Me
- End Sub
-
Try this.
Display MoreCode- Private Sub ListBox1_Change()
- Dim LstRow As Long
- UserForm1.ListBox2.Clear
- UserForm1.ListBox2.AddItem Sheets("Sheet1").Cells(ListBox1.ListIndex + 2, 2)
- With Sheet2
- LstRow = .Cells(.Rows.Count, "A").End(xlUp).Row + 1
- .Cells(LstRow, 1) = Me.ListBox1
- .Cells(LstRow, 2) = Sheets("Sheet1").Cells(ListBox1.ListIndex + 2, 2)
- End With
- End Sub
-
The" exit for" is the line the exits the loop after the first occurrence.
The code does not open the workbook, that error should not occur.
If you are attempting to open the workbook in the code, search for "Open workbook if not already open"
-
I have a code that copies and pastes a rang to outlook, I thought I would try using the print screen and pasting into outlook and it seems to do it.
See how it works for you.
Display MoreCode- Private Sub CommandButton1_Click()
- Dim doc As Object, rng As Range
- Application.SendKeys "(%{1068})"
- DoEvents
- 'ActiveSheet.Paste
- With CreateObject("Outlook.Application").CreateItem(0)
- .display
- Set doc = .GetInspector.WordEditor
- doc.Range(0, 0).Paste
- .To = "[email protected]"
- .Subject = "Send Email Body"
- End With
- End Sub
-
Give this a try,
Display MoreCode- Private Sub CommandButton1_Click()
- Dim wb As Workbook
- Dim sh As Worksheet, c As Range
- Dim rng As Range
- Set wb = Workbooks("Destination.xlsx")
- Set sh = wb.Sheets("Sheet2")
- With sh
- Set rng = .Range("B2:B" & .Cells(.Rows.Count, "B").End(xlUp).Row)
- For Each c In rng.Cells
- If UCase(c) = UCase(Me.Range("D5").Value) Then
- If UCase(c.Offset(, 3)) = UCase(Me.Range("D7")) Then
- If c.Offset(, 4) = Me.Range("D8") Then
- c.Offset(, 9) = "Calibration done"
- Exit For
- End If
- End If
- End If
- Next c
- End With
- End Sub
-
If you have a PDF printer you can print the userform
-
I removed most of the information in the database due to confidentiality reason as it is work involved
Your workbooks should show the original layout and what the required results should be.
Fill the sheets with phony data.
Supplying blank workbooks is not helpful
-
-
Oxgrid also has a great library of VBA code samples.
-
You can also place the page numbers in the sheets
-
Can you supply a sample workbook?
-
Hello,
What is the code doing and is it running slow?
Supply a sample workbook to go with the code so somebody can test it.
-
You need to remove .select
-
I don't get that result, but if you are getting 1 more than you want, you can always subtract 1 from `num`, you will have to make sure x does not equal zero though . Probably changing
if num>1
You don't want
for x=1 to 0
-
You have it copying e20:I34 but the range should be E21.
E20 is blank, so the code will keep pasting in the same cell.