You also forgot to mention that you have cross posted which means you have asked the same question in other forum(s).
Most will shut you down until you post the links to the other site where you cross posted.
Do so in all the sites.
Posts by jolivanes
-
-
Re: "Sorry I am posting all my questions here...."
Why? I am sure everyone here is glad to see you here. Don't be shy. Come back anytime.
The code from dangelor has you covered and is faster also.
In your code, if you change this
to this
and
to this
You would have had a home run.
You could also have the three lines from If to End If in a single line like so
But stick to dangelor's code as that is the way to go.
-
With the information provided!
Change all references where required.
Data depending, there are faster ways but this might be the easiest to understand.
Display MoreCode- Sub Maybe()
- Dim lc As Long, i As Long
- Dim sh1 As Worksheet, sh2 As Worksheet
- Set sh1 = Worksheets("Sheet1")
- Set sh2 = Worksheets("Sheet2")
- lc = sh1.Cells.Find("*", , , , xlByColumns, xlPrevious).Column
- Application.ScreenUpdating = False
- With sh1
- For i = lc To 1 Step -1
- If .Cells(1, i).Value = "RECEIVED" Then
- .Columns(i).Copy sh2.Cells(1, Columns.Count).End(xlToLeft).Offset(, 1)
- .Cells(1, i).EntireColumn.Delete Shift:=xlToLeft
- End If
- Next i
- End With
- Application.ScreenUpdating = True
- End Sub
-
If the amount of data is not too big, looping should be OK.
Display MoreCode- Sub Maybe()
- Dim sh1 As Worksheet, sh2 As Worksheet
- Dim i As Long
- Dim c As Range
- Set sh1 = Worksheets("database")
- Set sh2 = Worksheets("ORDERS")
- For i = 2 To sh2.Cells(Rows.Count, 1).End(xlUp).Row
- For Each c In sh1.Range("A2:A" & sh1.Cells(Rows.Count, 1).End(xlUp).Row)
- If c.Value = sh2.Cells(i, 1).Value Then c.Offset(, 3).Value = c.Offset(, 3).Value * sh2.Cells(i, 2).Value
- Next c
- Next i
- End Sub
-
You wrote a whole book but no specific requirement on what and where.
There is no way you'll need that many macros. Looping through Ranges and Sheets is a normal occurrence.
Just let us know what needs to happen, where it needs to happen and what the condition is.
The best is to attach a workbook with a before and after with an explanation on how the "after" was arrived at.
There should be sufficient data in the workbook to understand the needs.
-
"but the last two rows" I assume you mean Columns here.
Attach your workbook. Without seeing the code it is just guesswork on how everything is set.
-
With numerical values, something like this should work.
This is just an example as you should notice.
Display MoreCode- Sub Example()
- Dim c As Range
- For Each c In Range("A2:A" & Cells(Rows.Count, 1).End(xlUp).Row)
- Select Case c.Value
- Case 0 To 19
- c.Offset(, 2) = "Junior"
- Case 20 To 29
- c.Offset(, 2) = "Senior"
- Case 30 To 39
- c.Offset(, 2) = "Old Man"
- Case 40 To 49
- c.Offset(, 2) = "Very Old Man"
- Case Else
- c.Offset(, 2) = "Ancient"
- End Select
- Next c
- End Sub
-
Just an observation.
If a person is 70 years old, which Case would you take?
The first 4 are all true.
-
Set the width of the columns to a better "to fit" size.
-
-
We should not be advertising other sites but I hope it is allowed in this case.
-
Marsa,
Please dont start a new question in an existing thread. This is known as Thread hi-jacking. Start your own thread. Its Free! If you need to, you can link back to the thread that you thought might have been helpful.
This is from the Forum Rules.
-
http://www.snb-vba.eu/alfabet_en.html
look under the "p" for pixels
-
Thank you for letting us know that all is working as you want.
Good luck and stay safe
-
If you do want to do it after, this will do that.
Code- Sub Delete_Red_Cell_Columns()
- Dim i As Long
- Application.ScreenUpdating = False
- For i = Cells(8, Columns.Count).End(xlToLeft).Column To 1 Step -1 '<---- Change the 8 to a 7 for Row #7
- If Cells(8, i).Interior.Color = vbRed Then Cells(8, i).EntireColumn.Delete '<---- Change the 8 to a 7 for Row #7
- Next i
- Application.ScreenUpdating = True
- End Sub
-
Which code are you referring to? The code from Post #9 or Post #10?
If it is from Post #10, change this line
to this
If it is for the code from Post #9, it is mentioned on how to proceed at the end of that Post.
If you want to keep the cells red and have a separate macro to delete the Columns later, after checking maybe, let us know.
-
Multiple values in one go.
Code- Sub Maybe_Multiple_Values()
- Dim i As Long, delArr, j As Long
- delArr = Array("line", "group", "Heading") '<---- Put all the values to be deleted between double quotation marks in the array
- For i = Cells(8, Columns.Count).End(xlToLeft).Column To 1 Step -1 '<---- Change the 8 to a 7 for Row #7
- For j = LBound(delArr) To UBound(delArr)
- If Left(Cells(8, i), Len(delArr(j))) = delArr(j) Then Cells(8, i).Interior.Color = vbRed: Exit For '<---- Change both 8's to a 7 for Row #7
- Next j
- Next i
- End Sub
-
I assumed you mean Row 8 to be the Row in question.
If it is Row #7, change all the referenced cell numbers from 8 to 7
Right now it colors the cells red so you can check if the code does what you want it to do.
Code- Sub Maybe()
- Dim i As Long, strWord As String
- strWord = Application.InputBox("Enter the word to search for.", "Delete the columns with this word", Type:=2)
- For i = Cells(8, Columns.Count).End(xlToLeft).Column To 1 Step -1 '<---- Change the 8 to a 7 for Row #7
- If Left(Cells(8, i), Len(strWord)) = strWord Then Cells(8, i).Interior.Color = vbRed '<---- Change both 8's to a 7 for Row #7
- Next i
- End Sub
If you're happy, change this line
to
For future considerations.
Header rows are normally the Top Row unless specified to be different.
-
You say "the first word of a sentence".
I understand that to mean the first word of a string in a cell. Maybe not so.
As I previously asked, explain in detail or the best thing to do is attach a realistic workbook. Change privacy values if they exist in the workbook (names, addresses, email addresses and what have you)
-
Re: *Your code does not work
Yes it does.
Attach a workbook.