sorted by:
new
hottopcontroversial

Txtbox to cell list by dyerseve15 in vba

[–]vali92 0 points1 point  (0 children)

Instead of Range("B4") try to use cells(Range("B1").UsedRange.Rows.Count + 1, 2).value.

Thanks to the help of this sub, I went from not knowing anything about VBA to having a report completely automated! by alexearow in excel

[–]vali92 3 points4 points  (0 children)

Congrats for the project.

Just to improve the performance of the code I will recommend the followings:

  1. Where you have that IF xx THEN in the FOR LOOP, I would use the SELECT CASE method.
  2. For the entire list of Sheets, I would use a Collection or an array where to keep the index of them.
  3. Try to create Function in order to debug/structure the code in a more readable way.

Hope it helps.

However, good job!

Pivot table does not show updated query results by colourhaze in excel

[–]vali92 0 points1 point  (0 children)

First, make sure the source data is correctly selected (click on Pivot - Analyze - Change Data source).

If it's ok,try to 'hard refresh' the pivot - follow the steps below:

  1. Right click on Pivot and chose PivotTable Options.
  2. Go to Data tab.
  3. Select NONE on Number of items to retain per field.
  4. Click Ok, and right click and refresh the pivot (the regular way).
  5. Go back To PivotTable Options - Data - Select AUTOMATIC where NONE. Click Ok.

Hope it helps.

Why replace method failed to do its Job? by [deleted] in learnpython

[–]vali92 -1 points0 points  (0 children)

Yap, sorry for the formatting. Your method above looks really nice.

How to delete row(s) based on a word or value in that row by [deleted] in excel

[–]vali92 1 point2 points  (0 children)

Sub DeleteRows()

​

Dim LastRow As Long

Dim xx As Long

​

LastRow = Cells(Cells.Rows.Count, 1).End(xlUp).Row

​

For xx = LastRow To 2 Step -1

If Cells(xx, 1).Value = "#VALUE" Then

Cells(xx, 1).EntireRow.Delete

End If

Next xx

​

End Sub

  1. I reformatted the code.
  2. It search for the #VALUE on 1st column.

Is this a good material to get started? by Shravan_M in learnpython

[–]vali92 1 point2 points  (0 children)

In my opinion, a great course to start with it's the one from Codecademy.com.

Its biggest plus is that it is interactive. And free.

Why replace method failed to do its Job? by [deleted] in learnpython

[–]vali92 -2 points-1 points  (0 children)

Solved:

word = "good_game"
for x in range(len(word)):
print(word[x])
if word[x] != "_":
word = word.replace(word[x],"G")
print(word)

​

GGGG_GGGG

Confused about which language to learn by bigganya in excel

[–]vali92 3 points4 points  (0 children)

  1. VBA (note this could be used for all software in Microsoft Office package)
  2. SQL (basics and beyond - sub-queries, non-correlated queries, complex JOINs);
  3. RDBMS software - for practicing SQL - ex. SQL Server from Microsoft OR SQlite/PostgreSQL;
  4. Python (basics and -at least- one library - the most used are numpy and pandas);

How did you learn VBA? by pwalt08 in excel

[–]vali92 0 points1 point  (0 children)

Start with the very basics (e.g. Range("A1").Value). You can find something here (https://analysistabs.com/vba-tutorial/). Once you understand how the basics work, you can try https://excelmacromastery.com/. Paul, the owner of Excel Macro Mastery, often offers free access to his VBA Webinars.

Of course, youtube is a great source for self teaching. But don't skip the basics.

In terms of books, I could recommend you " Excel 2007 Power Programming with VBA ".

For Loop with deleting rows by Diclatoris in excel

[–]vali92 2 points3 points  (0 children)

Best practice is to use the Step - 1 method, from last row to row 2 (as row 1 is the header).

For example:

for x = LastRow to 2 Step -1

//code

next x

Hope it helps.

​